Synapse Physical Model

Schema for:

Model name: Synapse AdventureWorks
Author:
Version:
File name: Pepsi-Synapse_AdventureWorks_AFTER_inferring_PK-FK.hck.json
File path: /Users/rvanbruggen/Documents/HackoladeRepo/1-Customers/Pepsi/Pepsi-Synapse_AdventureWorks_AFTER_inferring_PK-FK.hck.json
Printed On: Fri Jul 07 2023 15:27:32 GMT+0200 (Central European Summer Time)
Created with: Hackolade - Polyglot data modeling for NoSQL databases, storage formats, REST APIs, and JSON in RDBMS
1. Model
3. Relationships
1. Model
1.1 Model Synapse AdventureWorks
1.1.1 Synapse AdventureWorks Entity Relationship Diagram
1.1.2 Synapse AdventureWorks Properties
1.1.2.1 Details tab
PropertyValue
Model nameSynapse AdventureWorks
Technical name
Description
Author
Version
Synchronization Ide42cbfb0-bba4-11ea-91a7-efa8d96c78e7
DB vendorSynapse
Modeling methodology
Tenant
Account ID
Resource group
Subscription
Preferred location
Tags
Performance level
Collation
Maintenance schedule
Geo-backup policy
Lineage capture
Polyglot models
Comments
1.1.3 Synapse AdventureWorks User-Defined Types
1.1.3.1 Column Name
1.1.3.1.1 Name properties
PropertyValue
Business Name Name
Technical name
Id
Type char
Subtype nvarchar
Has max length
Length 50
Default
Collate
Default constraint name
Comments
Not null
Masked with function
Primary key false
Unique
Foreign table
Foreign field
Relationship type
Relationship name
Cardinality
Min length
Pattern
Format
Enum
Faker function
Sample
Remarks
1.1.3.2 Column NameStyle
1.1.3.2.1 NameStyle properties
PropertyValue
Business Name NameStyle
Technical name
Id
Type numeric
Subtype bit
Default ((0))
Default constraint name
Comments
Not null true
Masked with function
Identity
Primary key false
Unique
Foreign table
Foreign field
Relationship type
Relationship name
Cardinality
Unit
Min value
Excl min
Max value
Excl max
Multiple of
Divisible by
Pattern
Enum
Faker function
Sample
Remarks
1.1.3.3 Column Phone
1.1.3.3.1 Phone properties
PropertyValue
Business Name Phone
Technical name
Id
Type char
Subtype nvarchar
Has max length
Length 25
Default
Collate
Default constraint name
Comments
Not null
Masked with function
Primary key false
Unique
Foreign table
Foreign field
Relationship type
Relationship name
Cardinality
Min length
Pattern
Format
Enum
Faker function
Sample
Remarks
1.1.3.4 Column Flag
1.1.3.4.1 Flag properties
PropertyValue
Business Name Flag
Technical name
Id
Type numeric
Subtype bit
Default 1
Default constraint name
Comments
Not null true
Masked with function
Identity
Primary key false
Unique
Foreign table
Foreign field
Relationship type
Relationship name
Cardinality
Unit
Min value
Excl min
Max value
Excl max
Multiple of
Divisible by
Pattern
Enum
Faker function
Sample
Remarks
1.1.3.5 Column OrderNumber
1.1.3.5.1 OrderNumber properties
PropertyValue
Business Name OrderNumber
Technical name
Id
Type char
Subtype nvarchar
Has max length
Length 25
Default
Collate
Default constraint name
Comments
Not null
Masked with function
Primary key false
Unique
Foreign table
Foreign field
Relationship type
Relationship name
Cardinality
Min length
Pattern
Format
Enum
Faker function
Sample
Remarks
1.1.3.6 Column AccountNumber
1.1.3.6.1 AccountNumber properties
PropertyValue
Business Name AccountNumber
Technical name
Id
Type char
Subtype nvarchar
Has max length
Length 15
Default
Collate
Default constraint name
Comments
Not null
Masked with function
Primary key false
Unique
Foreign table
Foreign field
Relationship type
Relationship name
Cardinality
Min length
Pattern
Format
Enum
Faker function
Sample
Remarks
2. Schemas
2.1 Schema dbo
2.1.1 dbo Properties
PropertyValue
Schema namedbo
Technical name
Activatedtrue
Comments
If not exist
Remarks
Database namehacksqlpool
2.1.2 dbo Tables
2.1.2.1 Table AdventureWorksDWBuildVersion
2.1.2.1.1 AdventureWorksDWBuildVersion Properties
PropertyValue
TableAdventureWorksDWBuildVersion
Technical name
Activatedtrue
Id
Schemadbo
Additional propertiesfalse
$ref
Comments
If not exist
Distributionround_robin
Persistenceregular
SQL pool indexingclustered columnstore index
Partition
CTAS query
Range for values
Boundary value
Remarks
2.1.2.1.2 AdventureWorksDWBuildVersion Column
ColumnTypeReqKeyDescriptionComments
DBVersionnvarchar(50)truepk
VersionDatedatetimetrue
2.1.2.1.2.1 Column DBVersion
2.1.2.1.2.1.1 DBVersion properties
PropertyValue
Business Name DBVersion
Technical name
Activated true
Id
Type char
Subtype nvarchar
Length 50
Default
Collate
Default constraint name
Comments
Not null true
Masked with function
Primary key true
Foreign table
Foreign field
Relationship type
Relationship name
Cardinality
Min length
Pattern
Format
Enum
Faker function
Sample 10.00.3590.01
Remarks
2.1.2.1.2.2 Column VersionDate
2.1.2.1.2.2.1 VersionDate properties
PropertyValue
Business Name VersionDate
Technical name
Activated true
Id
Type datetime
Subtype datetime
Comments
Not null true
Masked with function
Primary key
Unique
Pattern
Default
Default constraint name
Enum
Faker function
Sample 2012-07-07T19:49:42.000Z
Remarks
2.1.2.1.3 AdventureWorksDWBuildVersion Composite keys
PropertyValue
Primary key
[1] Constraint name
Key
Unique key
[1] Constraint name
Key
Alternate key
2.1.2.1.4 AdventureWorksDWBuildVersion JSON Schema
{
    "$schema": "http://json-schema.org/draft-04/schema#",
    "type": "object",
    "title": "AdventureWorksDWBuildVersion",
    "additionalProperties": false,
    "properties": {
        "DBVersion": {
            "type": "string"
        },
        "VersionDate": {
            "type": "string",
            "maxLength": 24
        }
    },
    "required": [
        "DBVersion",
        "VersionDate"
    ]
}
2.1.2.1.5 AdventureWorksDWBuildVersion JSON data
{
    "DBVersion": "10.00.3590.01",
    "VersionDate": "2012-07-07T19:49:42.000Z"
}
2.1.2.1.6 AdventureWorksDWBuildVersion Target Script
IF NOT EXISTS (SELECT * FROM sys.databases WHERE name = N'[hacksqlpool]')
begin
	CREATE DATABASE [hacksqlpool];
	USE [hacksqlpool];
end;

CREATE SCHEMA [dbo];

CREATE TABLE [dbo].[AdventureWorksDWBuildVersion] (
	[DBVersion] NVARCHAR(50) PRIMARY KEY NONCLUSTERED NOT ENFORCED NOT NULL,
	[VersionDate] DATETIME NOT NULL
)WITH (
	CLUSTERED COLUMNSTORE INDEX,
	DISTRIBUTION = ROUND_ROBIN
);
2.1.2.2 Table DatabaseLog
2.1.2.2.1 DatabaseLog Properties
PropertyValue
TableDatabaseLog
Technical name
Activatedtrue
Id
Schemadbo
Additional propertiesfalse
$ref
Comments
If not exist
Distributionround_robin
Persistenceregular
SQL pool indexingclustered columnstore index
Partition
CTAS query
Range for values
Boundary value
Remarks
2.1.2.2.2 DatabaseLog Column
ColumnTypeReqKeyDescriptionComments
DatabaseLogIDinttruepk
PostTimedatetimetrue
DatabaseUsernvarchar(128)true
Eventnvarchar(128)true
Schemanvarchar(128)false
Objectnvarchar(128)false
TSQLnvarchar(4000)true
2.1.2.2.2.1 Column DatabaseLogID
2.1.2.2.2.1.1 DatabaseLogID properties
PropertyValue
Business Name DatabaseLogID
Technical name
Activated true
Id
Type numeric
Subtype int
Default
Default constraint name
Comments
Not null true
Masked with function
Identity
Primary key true
Foreign table
Foreign field
Relationship type
Relationship name
Cardinality
Unit
Min value
Excl min
Max value
Excl max
Multiple of
Divisible by
Pattern
Enum
Faker function
Sample 99
Remarks
2.1.2.2.2.2 Column PostTime
2.1.2.2.2.2.1 PostTime properties
PropertyValue
Business Name PostTime
Technical name
Activated true
Id
Type datetime
Subtype datetime
Comments
Not null true
Masked with function
Primary key
Unique
Pattern
Default
Default constraint name
Enum
Faker function
Sample 2010-01-15T11:01:11.130Z
Remarks
2.1.2.2.2.3 Column DatabaseUser
2.1.2.2.2.3.1 DatabaseUser properties
PropertyValue
Business Name DatabaseUser
Technical name
Activated true
Id
Type char
Subtype nvarchar
Length 128
Default
Collate
Default constraint name
Comments
Not null true
Masked with function
Primary key
Unique
Foreign table
Foreign field
Relationship type
Relationship name
Cardinality
Min length
Pattern
Format
Enum
Faker function
Sample dbo
Remarks
2.1.2.2.2.4 Column Event
2.1.2.2.2.4.1 Event properties
PropertyValue
Business Name Event
Technical name
Activated true
Id
Type char
Subtype nvarchar
Length 128
Default
Collate
Default constraint name
Comments
Not null true
Masked with function
Primary key
Unique
Foreign table
Foreign field
Relationship type
Relationship name
Cardinality
Min length
Pattern
Format
Enum
Faker function
Sample ALTER_TABLE
Remarks
2.1.2.2.2.5 Column Schema
2.1.2.2.2.5.1 Schema properties
PropertyValue
Business Name Schema
Technical name
Activated true
Id
Type char
Subtype nvarchar
Length 128
Default
Collate
Default constraint name
Comments
Not null
Masked with function
Primary key
Unique
Foreign table
Foreign field
Relationship type
Relationship name
Cardinality
Min length
Pattern
Format
Enum
Faker function
Sample dbo
Remarks
2.1.2.2.2.6 Column Object
2.1.2.2.2.6.1 Object properties
PropertyValue
Business Name Object
Technical name
Activated true
Id
Type char
Subtype nvarchar
Length 128
Default
Collate
Default constraint name
Comments
Not null
Masked with function
Primary key
Unique
Foreign table
Foreign field
Relationship type
Relationship name
Cardinality
Min length
Pattern
Format
Enum
Faker function
Sample FactFinance
Remarks
2.1.2.2.2.7 Column TSQL
2.1.2.2.2.7.1 TSQL properties
PropertyValue
Business Name TSQL
Technical name
Activated true
Id
Type char
Subtype nvarchar
Length 4000
JSON Types string
Default
Collate
Default constraint name
Comments
Not null true
Masked with function
Primary key
Unique
Foreign table
Foreign field
Relationship type
Relationship name
Cardinality
Min length
Pattern
Format
Enum
Faker function
Sample ALTER TABLE [dbo].[FactFinance] ADD CONSTRAINT [FK_FactFinance_DimAccount] FOREIGN KEY ([AccountKey]) REFERENCES [dbo].[DimAccount] ([AccountKey]),CONSTRAINT [FK_FactFinance_DimDepartmentGroup] FOREIGN KEY ([DepartmentGroupKey]) REFERENCES [dbo].[DimDepartmentGroup] ([DepartmentGroupKey]),CONSTRAINT [FK_FactFinance_DimOrganization] FOREIGN KEY ([OrganizationKey]) REFERENCES [dbo].[DimOrganization] ([OrganizationKey]),CONSTRAINT [FK_FactFinance_DimScenario] FOREIGN KEY ([ScenarioKey]) REFERENCES [dbo].[DimScenario] ([ScenarioKey]),CONSTRAINT [FK_FactFinance_DimDate] FOREIGN KEY ([DateKey]) REFERENCES [dbo].[DimDate] ([DateKey]);
Remarks
2.1.2.2.3 DatabaseLog Composite keys
PropertyValue
Primary key
[1] Constraint name
Key
Unique key
[1] Constraint name
Key
Alternate key
2.1.2.2.4 DatabaseLog JSON Schema
{
    "$schema": "http://json-schema.org/draft-04/schema#",
    "type": "object",
    "title": "DatabaseLog",
    "additionalProperties": false,
    "properties": {
        "DatabaseLogID": {
            "type": "number"
        },
        "PostTime": {
            "type": "string",
            "maxLength": 24
        },
        "DatabaseUser": {
            "type": "string"
        },
        "Event": {
            "type": "string"
        },
        "Schema": {
            "type": "string"
        },
        "Object": {
            "type": "string"
        },
        "TSQL": {
            "type": "string"
        }
    },
    "required": [
        "DatabaseLogID",
        "PostTime",
        "DatabaseUser",
        "Event",
        "TSQL"
    ]
}
2.1.2.2.5 DatabaseLog JSON data
{
    "DatabaseLogID": 99,
    "PostTime": "2010-01-15T11:01:11.130Z",
    "DatabaseUser": "dbo",
    "Event": "ALTER_TABLE",
    "Schema": "dbo",
    "Object": "FactFinance",
    "TSQL": "ALTER TABLE [dbo].[FactFinance] ADD CONSTRAINT [FK_FactFinance_DimAccount] FOREIGN KEY ([AccountKey]) REFERENCES [dbo].[DimAccount] ([AccountKey]),CONSTRAINT [FK_FactFinance_DimDepartmentGroup] FOREIGN KEY ([DepartmentGroupKey]) REFERENCES [dbo].[DimDepartmentGroup] ([DepartmentGroupKey]),CONSTRAINT [FK_FactFinance_DimOrganization] FOREIGN KEY ([OrganizationKey]) REFERENCES [dbo].[DimOrganization] ([OrganizationKey]),CONSTRAINT [FK_FactFinance_DimScenario] FOREIGN KEY ([ScenarioKey]) REFERENCES [dbo].[DimScenario] ([ScenarioKey]),CONSTRAINT [FK_FactFinance_DimDate] FOREIGN KEY ([DateKey]) REFERENCES [dbo].[DimDate] ([DateKey]);"
}
2.1.2.2.6 DatabaseLog Target Script
IF NOT EXISTS (SELECT * FROM sys.databases WHERE name = N'[hacksqlpool]')
begin
	CREATE DATABASE [hacksqlpool];
	USE [hacksqlpool];
end;

CREATE SCHEMA [dbo];

CREATE TABLE [dbo].[DatabaseLog] (
	[DatabaseLogID] INT PRIMARY KEY NONCLUSTERED NOT ENFORCED NOT NULL,
	[PostTime] DATETIME NOT NULL,
	[DatabaseUser] NVARCHAR(128) NOT NULL,
	[Event] NVARCHAR(128) NOT NULL,
	[Schema] NVARCHAR(128),
	[Object] NVARCHAR(128),
	[TSQL] NVARCHAR(4000) NOT NULL
)WITH (
	CLUSTERED COLUMNSTORE INDEX,
	DISTRIBUTION = ROUND_ROBIN
);
2.1.2.3 Table DimAccount
2.1.2.3.1 DimAccount Properties
PropertyValue
TableDimAccount
Technical name
Activatedtrue
Id
Schemadbo
Additional propertiesfalse
$ref
Comments
If not exist
Distributionround_robin
Persistenceregular
SQL pool indexingclustered columnstore index
Partition
CTAS query
Range for values
Boundary value
Remarks
2.1.2.3.2 DimAccount Column
ColumnTypeReqKeyDescriptionComments
AccountKeyinttruepk, dk
ParentAccountKeyintfalsefk
AccountCodeAlternateKeyintfalse
ParentAccountCodeAlternateKeyintfalsefk
AccountDescriptionnvarchar(50)false
AccountTypenvarchar(50)false
Operatornvarchar(50)false
CustomMembersnvarchar(300)false
ValueTypenvarchar(50)false
CustomMemberOptionsnvarchar(200)false
2.1.2.3.2.1 Column AccountKey
2.1.2.3.2.1.1 AccountKey properties
PropertyValue
Business Name AccountKey
Technical name
Activated true
Id
Type numeric
Subtype int
Default
Default constraint name
Comments
Not null true
Masked with function
Identity
Primary key true
Foreign table
Foreign field
Relationship type
Relationship name
Cardinality
Unit
Min value
Excl min
Max value
Excl max
Multiple of
Divisible by
Pattern
Enum
Faker function
Sample 101
Remarks
2.1.2.3.2.2 Column ParentAccountKey
2.1.2.3.2.2.1 ParentAccountKey properties
PropertyValue
Business Name ParentAccountKey
Technical name
Activated true
Id
Type numeric
Subtype int
Default
Default constraint name
Comments
Not null
Masked with function
Identity
Primary key
Unique
Foreign table DimAccount
Foreign field AccountKey
Relationship type Foreign Key
Relationship name fk DimAccount.ParentAccountKey to DimAccount.AccountKey
Cardinality 1..n
Unit
Min value
Excl min
Max value
Excl max
Multiple of
Divisible by
Pattern
Enum
Faker function
Sample 51
Remarks
2.1.2.3.2.3 Column AccountCodeAlternateKey
2.1.2.3.2.3.1 AccountCodeAlternateKey properties
PropertyValue
Business Name AccountCodeAlternateKey
Technical name
Activated true
Id
Type numeric
Subtype int
Default
Default constraint name
Comments
Not null
Masked with function
Identity
Primary key
Unique
Foreign table
Foreign field
Relationship type
Relationship name
Cardinality
Unit
Min value
Excl min
Max value
Excl max
Multiple of
Divisible by
Pattern
Enum
Faker function
Sample 4200
Remarks
2.1.2.3.2.4 Column ParentAccountCodeAlternateKey
2.1.2.3.2.4.1 ParentAccountCodeAlternateKey properties
PropertyValue
Business Name ParentAccountCodeAlternateKey
Technical name
Activated true
Id
Type numeric
Subtype int
Default
Default constraint name
Comments
Not null
Masked with function
Identity
Primary key
Unique
Foreign table DimAccount
Foreign field AccountKey
Relationship type Foreign Key
Relationship name fk DimAccount.ParentAccountCodeAlternateKey to DimAccount.AccountKey
Cardinality 1..n
Unit
Min value
Excl min
Max value
Excl max
Multiple of
Divisible by
Pattern
Enum
Faker function
Sample 4110
Remarks
2.1.2.3.2.5 Column AccountDescription
2.1.2.3.2.5.1 AccountDescription properties
PropertyValue
Business Name AccountDescription
Technical name
Activated true
Id
Type char
Subtype nvarchar
Length 50
Default
Collate
Default constraint name
Comments
Not null
Masked with function
Primary key
Unique
Foreign table
Foreign field
Relationship type
Relationship name
Cardinality
Min length
Pattern
Format
Enum
Faker function
Sample Trade Sales
Remarks
2.1.2.3.2.6 Column AccountType
2.1.2.3.2.6.1 AccountType properties
PropertyValue
Business Name AccountType
Technical name
Activated true
Id
Type char
Subtype nvarchar
Length 50
Default
Collate
Default constraint name
Comments
Not null
Masked with function
Primary key
Unique
Foreign table
Foreign field
Relationship type
Relationship name
Cardinality
Min length
Pattern
Format
Enum
Faker function
Sample Revenue
Remarks
2.1.2.3.2.7 Column Operator
2.1.2.3.2.7.1 Operator properties
PropertyValue
Business Name Operator
Technical name
Activated true
Id
Type char
Subtype nvarchar
Length 50
Default
Collate
Default constraint name
Comments
Not null
Masked with function
Primary key
Unique
Foreign table
Foreign field
Relationship type
Relationship name
Cardinality
Min length
Pattern
Format
Enum
Faker function
Sample +
Remarks
2.1.2.3.2.8 Column CustomMembers
2.1.2.3.2.8.1 CustomMembers properties
PropertyValue
Business Name CustomMembers
Technical name
Activated true
Id
Type char
Subtype nvarchar
Length 300
Default
Collate
Default constraint name
Comments
Not null
Masked with function
Primary key
Unique
Foreign table
Foreign field
Relationship type
Relationship name
Cardinality
Min length
Pattern
Format
Enum
Faker function
Sample [Account].[Accounts].[Account Level 04].&[50]/[Account].[Accounts].[Account Level 02].&[97]
Remarks
2.1.2.3.2.9 Column ValueType
2.1.2.3.2.9.1 ValueType properties
PropertyValue
Business Name ValueType
Technical name
Activated true
Id
Type char
Subtype nvarchar
Length 50
Default
Collate
Default constraint name
Comments
Not null
Masked with function
Primary key
Unique
Foreign table
Foreign field
Relationship type
Relationship name
Cardinality
Min length
Pattern
Format
Enum
Faker function
Sample Currency
Remarks
2.1.2.3.2.10 Column CustomMemberOptions
2.1.2.3.2.10.1 CustomMemberOptions properties
PropertyValue
Business Name CustomMemberOptions
Technical name
Activated true
Id
Type char
Subtype nvarchar
Length 200
Default
Collate
Default constraint name
Comments
Not null
Masked with function
Primary key
Unique
Foreign table
Foreign field
Relationship type
Relationship name
Cardinality
Min length
Pattern
Format
Enum
Faker function
Sample
Remarks
2.1.2.3.3 DimAccount Composite keys
PropertyValue
Primary key
[1] Constraint name
Key
Unique key
[1] Constraint name
Key
Alternate key
2.1.2.3.4 DimAccount JSON Schema
{
    "$schema": "http://json-schema.org/draft-04/schema#",
    "type": "object",
    "title": "DimAccount",
    "additionalProperties": false,
    "properties": {
        "AccountKey": {
            "type": "number"
        },
        "ParentAccountKey": {
            "type": "number"
        },
        "AccountCodeAlternateKey": {
            "type": "number"
        },
        "ParentAccountCodeAlternateKey": {
            "type": "number"
        },
        "AccountDescription": {
            "type": "string"
        },
        "AccountType": {
            "type": "string"
        },
        "Operator": {
            "type": "string"
        },
        "CustomMembers": {
            "type": "string"
        },
        "ValueType": {
            "type": "string"
        },
        "CustomMemberOptions": {
            "type": "string"
        }
    },
    "required": [
        "AccountKey"
    ]
}
2.1.2.3.5 DimAccount JSON data
{
    "AccountKey": 101,
    "ParentAccountKey": 51,
    "AccountCodeAlternateKey": 4200,
    "ParentAccountCodeAlternateKey": 4110,
    "AccountDescription": "Trade Sales",
    "AccountType": "Revenue",
    "Operator": "+",
    "CustomMembers": "[Account].[Accounts].[Account Level 04].&[50]/[Account].[Accounts].[Account Level 02].&[97]",
    "ValueType": "Currency",
    "CustomMemberOptions": "Lorem"
}
2.1.2.3.6 DimAccount Target Script
IF NOT EXISTS (SELECT * FROM sys.databases WHERE name = N'[hacksqlpool]')
begin
	CREATE DATABASE [hacksqlpool];
	USE [hacksqlpool];
end;

CREATE SCHEMA [dbo];

CREATE TABLE [dbo].[DimAccount] (
	[AccountKey] INT PRIMARY KEY NONCLUSTERED NOT ENFORCED NOT NULL,
	[ParentAccountKey] INT,
	[AccountCodeAlternateKey] INT,
	[ParentAccountCodeAlternateKey] INT,
	[AccountDescription] NVARCHAR(50),
	[AccountType] NVARCHAR(50),
	[Operator] NVARCHAR(50),
	[CustomMembers] NVARCHAR(300),
	[ValueType] NVARCHAR(50),
	[CustomMemberOptions] NVARCHAR(200)
)WITH (
	CLUSTERED COLUMNSTORE INDEX,
	DISTRIBUTION = ROUND_ROBIN
);
2.1.2.4 Table DimCurrency
2.1.2.4.1 DimCurrency Properties
PropertyValue
TableDimCurrency
Technical name
Activatedtrue
Id
Schemadbo
Additional propertiesfalse
$ref
Comments
If not exist
Distributionround_robin
Persistenceregular
SQL pool indexingclustered columnstore index
Partition
CTAS query
Range for values
Boundary value
Remarks
2.1.2.4.2 DimCurrency Column
ColumnTypeReqKeyDescriptionComments
CurrencyKeyinttruepk, dk
CurrencyAlternateKeynchar(3)true
CurrencyNamenvarchar(50)true
2.1.2.4.2.1 Column CurrencyKey
2.1.2.4.2.1.1 CurrencyKey properties
PropertyValue
Business Name CurrencyKey
Technical name
Activated true
Id
Type numeric
Subtype int
Default
Default constraint name
Comments
Not null true
Masked with function
Identity
Primary key true
Foreign table
Foreign field
Relationship type
Relationship name
Cardinality
Unit
Min value
Excl min
Max value
Excl max
Multiple of
Divisible by
Pattern
Enum
Faker function
Sample 101
Remarks
2.1.2.4.2.2 Column CurrencyAlternateKey
2.1.2.4.2.2.1 CurrencyAlternateKey properties
PropertyValue
Business Name CurrencyAlternateKey
Technical name
Activated true
Id
Type char
Subtype nchar
Length 3
Default
Collate
Default constraint name
Comments
Not null true
Masked with function
Primary key
Unique
Foreign table
Foreign field
Relationship type
Relationship name
Cardinality
Min length
Pattern
Format
Enum
Faker function
Sample KRW
Remarks
2.1.2.4.2.3 Column CurrencyName
2.1.2.4.2.3.1 CurrencyName properties
PropertyValue
Business Name CurrencyName
Technical name
Activated true
Id
Type char
Subtype nvarchar
Length 50
Default
Collate
Default constraint name
Comments
Not null true
Masked with function
Primary key
Unique
Foreign table
Foreign field
Relationship type
Relationship name
Cardinality
Min length
Pattern
Format
Enum
Faker function
Sample Won
Remarks
2.1.2.4.3 DimCurrency Composite keys
PropertyValue
Primary key
[1] Constraint name
Key
Unique key
[1] Constraint name
Key
Alternate key
2.1.2.4.4 DimCurrency JSON Schema
{
    "$schema": "http://json-schema.org/draft-04/schema#",
    "type": "object",
    "title": "DimCurrency",
    "additionalProperties": false,
    "properties": {
        "CurrencyKey": {
            "type": "number"
        },
        "CurrencyAlternateKey": {
            "type": "string"
        },
        "CurrencyName": {
            "type": "string"
        }
    },
    "required": [
        "CurrencyKey",
        "CurrencyAlternateKey",
        "CurrencyName"
    ]
}
2.1.2.4.5 DimCurrency JSON data
{
    "CurrencyKey": 101,
    "CurrencyAlternateKey": "KRW",
    "CurrencyName": "Won"
}
2.1.2.4.6 DimCurrency Target Script
IF NOT EXISTS (SELECT * FROM sys.databases WHERE name = N'[hacksqlpool]')
begin
	CREATE DATABASE [hacksqlpool];
	USE [hacksqlpool];
end;

CREATE SCHEMA [dbo];

CREATE TABLE [dbo].[DimCurrency] (
	[CurrencyKey] INT PRIMARY KEY NONCLUSTERED NOT ENFORCED NOT NULL,
	[CurrencyAlternateKey] NCHAR(3) NOT NULL,
	[CurrencyName] NVARCHAR(50) NOT NULL
)WITH (
	CLUSTERED COLUMNSTORE INDEX,
	DISTRIBUTION = ROUND_ROBIN
);
2.1.2.5 Table DimCustomer
2.1.2.5.1 DimCustomer Properties
PropertyValue
TableDimCustomer
Technical name
Activatedtrue
Id
Schemadbo
Additional propertiesfalse
$ref
Comments
If not exist
Distributionround_robin
Persistenceregular
SQL pool indexingclustered columnstore index
Partition
CTAS query
Range for values
Boundary value
Remarks
2.1.2.5.2 DimCustomer Column
ColumnTypeReqKeyDescriptionComments
CustomerKeyinttruepk, dk
GeographyKeyintfalsefk
CustomerAlternateKeynvarchar(15)true
Titlenvarchar(8)false
FirstNamenvarchar(50)false
MiddleNamenvarchar(50)false
LastNamenvarchar(50)false
NameStylebitfalse
BirthDatedatefalse
MaritalStatusnchar(1)false
Suffixnvarchar(10)false
Gendernvarchar(1)false
EmailAddressnvarchar(50)false
YearlyIncomemoneyfalse
TotalChildrentinyintfalse
NumberChildrenAtHometinyintfalse
EnglishEducationnvarchar(40)false
SpanishEducationnvarchar(40)false
FrenchEducationnvarchar(40)false
EnglishOccupationnvarchar(100)false
SpanishOccupationnvarchar(100)false
FrenchOccupationnvarchar(100)false
HouseOwnerFlagnchar(1)false
NumberCarsOwnedtinyintfalse
AddressLine1nvarchar(120)false
AddressLine2nvarchar(120)false
Phonenvarchar(20)false
DateFirstPurchasedatefalse
CommuteDistancenvarchar(15)false
2.1.2.5.2.1 Column CustomerKey
2.1.2.5.2.1.1 CustomerKey properties
PropertyValue
Business Name CustomerKey
Technical name
Activated true
Id
Type numeric
Subtype int
Default
Default constraint name
Comments
Not null true
Masked with function
Identity
Primary key true
Foreign table
Foreign field
Relationship type
Relationship name
Cardinality
Unit
Min value
Excl min
Max value
Excl max
Multiple of
Divisible by
Pattern
Enum
Faker function
Sample 17900
Remarks
2.1.2.5.2.2 Column GeographyKey
2.1.2.5.2.2.1 GeographyKey properties
PropertyValue
Business Name GeographyKey
Technical name
Activated true
Id
Type numeric
Subtype int
Default
Default constraint name
Comments
Not null
Masked with function
Identity
Primary key
Unique
Foreign table DimGeography
Foreign field GeographyKey
Relationship type Foreign Key
Relationship name fk DimCustomer.GeographyKey to DimGeography.GeographyKey
Cardinality 1..n
Unit
Min value
Excl min
Max value
Excl max
Multiple of
Divisible by
Pattern
Enum
Faker function
Sample 204
Remarks
2.1.2.5.2.3 Column CustomerAlternateKey
2.1.2.5.2.3.1 CustomerAlternateKey properties
PropertyValue
Business Name CustomerAlternateKey
Technical name
Activated true
Id
Type char
Subtype nvarchar
Length 15
Default
Collate
Default constraint name
Comments
Not null true
Masked with function
Primary key
Unique
Foreign table
Foreign field
Relationship type
Relationship name
Cardinality
Min length
Pattern
Format
Enum
Faker function
Sample AW00017900
Remarks
2.1.2.5.2.4 Column Title
2.1.2.5.2.4.1 Title properties
PropertyValue
Business Name Title
Technical name
Activated true
Id
Type char
Subtype nvarchar
Length 8
Default
Collate
Default constraint name
Comments
Not null
Masked with function
Primary key
Unique
Foreign table
Foreign field
Relationship type
Relationship name
Cardinality
Min length
Pattern
Format
Enum
Faker function
Sample Ms.
Remarks
2.1.2.5.2.5 Column FirstName
2.1.2.5.2.5.1 FirstName properties
PropertyValue
Business Name FirstName
Technical name
Activated true
Id
Type char
Subtype nvarchar
Length 50
Default
Collate
Default constraint name
Comments
Not null
Masked with function
Primary key
Unique
Foreign table
Foreign field
Relationship type
Relationship name
Cardinality
Min length
Pattern
Format
Enum
Faker function
Sample Victoria
Remarks
2.1.2.5.2.6 Column MiddleName
2.1.2.5.2.6.1 MiddleName properties
PropertyValue
Business Name MiddleName
Technical name
Activated true
Id
Type char
Subtype nvarchar
Length 50
Default
Collate
Default constraint name
Comments
Not null
Masked with function
Primary key
Unique
Foreign table
Foreign field
Relationship type
Relationship name
Cardinality
Min length
Pattern
Format
Enum
Faker function
Sample L.
Remarks
2.1.2.5.2.7 Column LastName
2.1.2.5.2.7.1 LastName properties
PropertyValue
Business Name LastName
Technical name
Activated true
Id
Type char
Subtype nvarchar
Length 50
Default
Collate
Default constraint name
Comments
Not null
Masked with function
Primary key
Unique
Foreign table
Foreign field
Relationship type
Relationship name
Cardinality
Min length
Pattern
Format
Enum
Faker function
Sample Snowden
Remarks
2.1.2.5.2.8 Column NameStyle
2.1.2.5.2.8.1 NameStyle properties
PropertyValue
Business Name NameStyle
Technical name
Activated true
Id
Type numeric
Subtype bit
Default
Default constraint name
Comments
Not null
Masked with function
Identity
Primary key
Unique
Foreign table
Foreign field
Relationship type
Relationship name
Cardinality
Unit
Min value
Excl min
Max value
Excl max
Multiple of
Divisible by
Pattern
Enum
Faker function
Sample false
Remarks
2.1.2.5.2.9 Column BirthDate
2.1.2.5.2.9.1 BirthDate properties
PropertyValue
Business Name BirthDate
Technical name
Activated true
Id
Type datetime
Subtype date
Comments
Not null
Masked with function
Primary key
Unique
Pattern
Default
Default constraint name
Enum
Faker function
Sample 1942-08-06T00:00:00.000Z
Remarks
2.1.2.5.2.10 Column MaritalStatus
2.1.2.5.2.10.1 MaritalStatus properties
PropertyValue
Business Name MaritalStatus
Technical name
Activated true
Id
Type char
Subtype nchar
Length 1
Default
Collate
Default constraint name
Comments
Not null
Masked with function
Primary key
Unique
Foreign table
Foreign field
Relationship type
Relationship name
Cardinality
Min length
Pattern
Format
Enum
Faker function
Sample S
Remarks
2.1.2.5.2.11 Column Suffix
2.1.2.5.2.11.1 Suffix properties
PropertyValue
Business Name Suffix
Technical name
Activated true
Id
Type char
Subtype nvarchar
Length 10
Default
Collate
Default constraint name
Comments
Not null
Masked with function
Primary key
Unique
Foreign table
Foreign field
Relationship type
Relationship name
Cardinality
Min length
Pattern
Format
Enum
Faker function
Sample
Remarks
2.1.2.5.2.12 Column Gender
2.1.2.5.2.12.1 Gender properties
PropertyValue
Business Name Gender
Technical name
Activated true
Id
Type char
Subtype nvarchar
Length 1
Default
Collate
Default constraint name
Comments
Not null
Masked with function
Primary key
Unique
Foreign table
Foreign field
Relationship type
Relationship name
Cardinality
Min length
Pattern
Format
Enum
Faker function
Sample F
Remarks
2.1.2.5.2.13 Column EmailAddress
2.1.2.5.2.13.1 EmailAddress properties
PropertyValue
Business Name EmailAddress
Technical name
Activated true
Id
Type char
Subtype nvarchar
Length 50
Default
Collate
Default constraint name
Comments
Not null
Masked with function
Primary key
Unique
Foreign table
Foreign field
Relationship type
Relationship name
Cardinality
Min length
Pattern
Format
Enum
Faker function
Sample victoria0@adventure-works.com
Remarks
2.1.2.5.2.14 Column YearlyIncome
2.1.2.5.2.14.1 YearlyIncome properties
PropertyValue
Business Name YearlyIncome
Technical name
Activated true
Id
Type numeric
Subtype money
Default
Default constraint name
Comments
Not null
Masked with function
Identity
Primary key
Unique
Foreign table
Foreign field
Relationship type
Relationship name
Cardinality
Unit
Min value
Excl min
Max value
Excl max
Multiple of
Divisible by
Pattern
Enum
Faker function
Sample 10000
Remarks
2.1.2.5.2.15 Column TotalChildren
2.1.2.5.2.15.1 TotalChildren properties
PropertyValue
Business Name TotalChildren
Technical name
Activated true
Id
Type numeric
Subtype tinyint
Default
Default constraint name
Comments
Not null
Masked with function
Identity
Primary key
Unique
Foreign table
Foreign field
Relationship type
Relationship name
Cardinality
Unit
Min value
Excl min
Max value
Excl max
Multiple of
Divisible by
Pattern
Enum
Faker function
Sample 3
Remarks
2.1.2.5.2.16 Column NumberChildrenAtHome
2.1.2.5.2.16.1 NumberChildrenAtHome properties
PropertyValue
Business Name NumberChildrenAtHome
Technical name
Activated true
Id
Type numeric
Subtype tinyint
Default
Default constraint name
Comments
Not null
Masked with function
Identity
Primary key
Unique
Foreign table
Foreign field
Relationship type
Relationship name
Cardinality
Unit
Min value
Excl min
Max value
Excl max
Multiple of
Divisible by
Pattern
Enum
Faker function
Sample 0
Remarks
2.1.2.5.2.17 Column EnglishEducation
2.1.2.5.2.17.1 EnglishEducation properties
PropertyValue
Business Name EnglishEducation
Technical name
Activated true
Id
Type char
Subtype nvarchar
Length 40
Default
Collate
Default constraint name
Comments
Not null
Masked with function
Primary key
Unique
Foreign table
Foreign field
Relationship type
Relationship name
Cardinality
Min length
Pattern
Format
Enum
Faker function
Sample Partial College
Remarks
2.1.2.5.2.18 Column SpanishEducation
2.1.2.5.2.18.1 SpanishEducation properties
PropertyValue
Business Name SpanishEducation
Technical name
Activated true
Id
Type char
Subtype nvarchar
Length 40
Default
Collate
Default constraint name
Comments
Not null
Masked with function
Primary key
Unique
Foreign table
Foreign field
Relationship type
Relationship name
Cardinality
Min length
Pattern
Format
Enum
Faker function
Sample Estudios universitarios (en curso)
Remarks
2.1.2.5.2.19 Column FrenchEducation
2.1.2.5.2.19.1 FrenchEducation properties
PropertyValue
Business Name FrenchEducation
Technical name
Activated true
Id
Type char
Subtype nvarchar
Length 40
Default
Collate
Default constraint name
Comments
Not null
Masked with function
Primary key
Unique
Foreign table
Foreign field
Relationship type
Relationship name
Cardinality
Min length
Pattern
Format
Enum
Faker function
Sample Baccalauréat
Remarks
2.1.2.5.2.20 Column EnglishOccupation
2.1.2.5.2.20.1 EnglishOccupation properties
PropertyValue
Business Name EnglishOccupation
Technical name
Activated true
Id
Type char
Subtype nvarchar
Length 100
Default
Collate
Default constraint name
Comments
Not null
Masked with function
Primary key
Unique
Foreign table
Foreign field
Relationship type
Relationship name
Cardinality
Min length
Pattern
Format
Enum
Faker function
Sample Manual
Remarks
2.1.2.5.2.21 Column SpanishOccupation
2.1.2.5.2.21.1 SpanishOccupation properties
PropertyValue
Business Name SpanishOccupation
Technical name
Activated true
Id
Type char
Subtype nvarchar
Length 100
Default
Collate
Default constraint name
Comments
Not null
Masked with function
Primary key
Unique
Foreign table
Foreign field
Relationship type
Relationship name
Cardinality
Min length
Pattern
Format
Enum
Faker function
Sample Obrero
Remarks
2.1.2.5.2.22 Column FrenchOccupation
2.1.2.5.2.22.1 FrenchOccupation properties
PropertyValue
Business Name FrenchOccupation
Technical name
Activated true
Id
Type char
Subtype nvarchar
Length 100
Default
Collate
Default constraint name
Comments
Not null
Masked with function
Primary key
Unique
Foreign table
Foreign field
Relationship type
Relationship name
Cardinality
Min length
Pattern
Format
Enum
Faker function
Sample Ouvrier
Remarks
2.1.2.5.2.23 Column HouseOwnerFlag
2.1.2.5.2.23.1 HouseOwnerFlag properties
PropertyValue
Business Name HouseOwnerFlag
Technical name
Activated true
Id
Type char
Subtype nchar
Length 1
Default
Collate
Default constraint name
Comments
Not null
Masked with function
Primary key
Unique
Foreign table
Foreign field
Relationship type
Relationship name
Cardinality
Min length
Pattern
Format
Enum
Faker function
Sample 1
Remarks
2.1.2.5.2.24 Column NumberCarsOwned
2.1.2.5.2.24.1 NumberCarsOwned properties
PropertyValue
Business Name NumberCarsOwned
Technical name
Activated true
Id
Type numeric
Subtype tinyint
Default
Default constraint name
Comments
Not null
Masked with function
Identity
Primary key
Unique
Foreign table
Foreign field
Relationship type
Relationship name
Cardinality
Unit
Min value
Excl min
Max value
Excl max
Multiple of
Divisible by
Pattern
Enum
Faker function
Sample 1
Remarks
2.1.2.5.2.25 Column AddressLine1
2.1.2.5.2.25.1 AddressLine1 properties
PropertyValue
Business Name AddressLine1
Technical name
Activated true
Id
Type char
Subtype nvarchar
Length 120
Default
Collate
Default constraint name
Comments
Not null
Masked with function
Primary key
Unique
Foreign table
Foreign field
Relationship type
Relationship name
Cardinality
Min length
Pattern
Format
Enum
Faker function
Sample 8, rue de l´Avenir
Remarks
2.1.2.5.2.26 Column AddressLine2
2.1.2.5.2.26.1 AddressLine2 properties
PropertyValue
Business Name AddressLine2
Technical name
Activated true
Id
Type char
Subtype nvarchar
Length 120
Default
Collate
Default constraint name
Comments
Not null
Masked with function
Primary key
Unique
Foreign table
Foreign field
Relationship type
Relationship name
Cardinality
Min length
Pattern
Format
Enum
Faker function
Sample # 103
Remarks
2.1.2.5.2.27 Column Phone
2.1.2.5.2.27.1 Phone properties
PropertyValue
Business Name Phone
Technical name
Activated true
Id
Type char
Subtype nvarchar
Length 20
Default
Collate
Default constraint name
Comments
Not null
Masked with function
Primary key
Unique
Foreign table
Foreign field
Relationship type
Relationship name
Cardinality
Min length
Pattern
Format
Enum
Faker function
Sample 574-555-0100
Remarks
2.1.2.5.2.28 Column DateFirstPurchase
2.1.2.5.2.28.1 DateFirstPurchase properties
PropertyValue
Business Name DateFirstPurchase
Technical name
Activated true
Id
Type datetime
Subtype date
Comments
Not null
Masked with function
Primary key
Unique
Pattern
Default
Default constraint name
Enum
Faker function
Sample 2003-06-04T00:00:00.000Z
Remarks
2.1.2.5.2.29 Column CommuteDistance
2.1.2.5.2.29.1 CommuteDistance properties
PropertyValue
Business Name CommuteDistance
Technical name
Activated true
Id
Type char
Subtype nvarchar
Length 15
Default
Collate
Default constraint name
Comments
Not null
Masked with function
Primary key
Unique
Foreign table
Foreign field
Relationship type
Relationship name
Cardinality
Min length
Pattern
Format
Enum
Faker function
Sample 1-2 Miles
Remarks
2.1.2.5.3 DimCustomer Composite keys
PropertyValue
Primary key
[1] Constraint name
Key
Unique key
[1] Constraint name
Key
Alternate key
2.1.2.5.4 DimCustomer JSON Schema
{
    "$schema": "http://json-schema.org/draft-04/schema#",
    "type": "object",
    "title": "DimCustomer",
    "additionalProperties": false,
    "properties": {
        "CustomerKey": {
            "type": "number"
        },
        "GeographyKey": {
            "type": "number"
        },
        "CustomerAlternateKey": {
            "type": "string"
        },
        "Title": {
            "type": "string"
        },
        "FirstName": {
            "type": "string"
        },
        "MiddleName": {
            "type": "string"
        },
        "LastName": {
            "type": "string"
        },
        "NameStyle": {
            "type": "number"
        },
        "BirthDate": {
            "type": "string",
            "maxLength": 24
        },
        "MaritalStatus": {
            "type": "string"
        },
        "Suffix": {
            "type": "string"
        },
        "Gender": {
            "type": "string"
        },
        "EmailAddress": {
            "type": "string"
        },
        "YearlyIncome": {
            "type": "number"
        },
        "TotalChildren": {
            "type": "number"
        },
        "NumberChildrenAtHome": {
            "type": "number"
        },
        "EnglishEducation": {
            "type": "string"
        },
        "SpanishEducation": {
            "type": "string"
        },
        "FrenchEducation": {
            "type": "string"
        },
        "EnglishOccupation": {
            "type": "string"
        },
        "SpanishOccupation": {
            "type": "string"
        },
        "FrenchOccupation": {
            "type": "string"
        },
        "HouseOwnerFlag": {
            "type": "string"
        },
        "NumberCarsOwned": {
            "type": "number"
        },
        "AddressLine1": {
            "type": "string"
        },
        "AddressLine2": {
            "type": "string"
        },
        "Phone": {
            "type": "string"
        },
        "DateFirstPurchase": {
            "type": "string",
            "maxLength": 24
        },
        "CommuteDistance": {
            "type": "string"
        }
    },
    "required": [
        "CustomerKey",
        "CustomerAlternateKey"
    ]
}
2.1.2.5.5 DimCustomer JSON data
{
    "CustomerKey": 17900,
    "GeographyKey": 204,
    "CustomerAlternateKey": "AW00017900",
    "Title": "Ms.",
    "FirstName": "Victoria",
    "MiddleName": "L.",
    "LastName": "Snowden",
    "NameStyle": false,
    "BirthDate": "1942-08-06T00:00:00.000Z",
    "MaritalStatus": "S",
    "Suffix": "Lorem",
    "Gender": "F",
    "EmailAddress": "victoria0@adventure-works.com",
    "YearlyIncome": 10000,
    "TotalChildren": 3,
    "NumberChildrenAtHome": 0,
    "EnglishEducation": "Partial College",
    "SpanishEducation": "Estudios universitarios (en curso)",
    "FrenchEducation": "Baccalauréat",
    "EnglishOccupation": "Manual",
    "SpanishOccupation": "Obrero",
    "FrenchOccupation": "Ouvrier",
    "HouseOwnerFlag": "1",
    "NumberCarsOwned": 1,
    "AddressLine1": "8, rue de l´Avenir",
    "AddressLine2": "# 103",
    "Phone": "574-555-0100",
    "DateFirstPurchase": "2003-06-04T00:00:00.000Z",
    "CommuteDistance": "1-2 Miles"
}
2.1.2.5.6 DimCustomer Target Script
IF NOT EXISTS (SELECT * FROM sys.databases WHERE name = N'[hacksqlpool]')
begin
	CREATE DATABASE [hacksqlpool];
	USE [hacksqlpool];
end;

CREATE SCHEMA [dbo];

CREATE TABLE [dbo].[DimCustomer] (
	[CustomerKey] INT PRIMARY KEY NONCLUSTERED NOT ENFORCED NOT NULL,
	[GeographyKey] INT,
	[CustomerAlternateKey] NVARCHAR(15) NOT NULL,
	[Title] NVARCHAR(8),
	[FirstName] NVARCHAR(50),
	[MiddleName] NVARCHAR(50),
	[LastName] NVARCHAR(50),
	[NameStyle] BIT,
	[BirthDate] DATE,
	[MaritalStatus] NCHAR(1),
	[Suffix] NVARCHAR(10),
	[Gender] NVARCHAR(1),
	[EmailAddress] NVARCHAR(50),
	[YearlyIncome] MONEY,
	[TotalChildren] TINYINT,
	[NumberChildrenAtHome] TINYINT,
	[EnglishEducation] NVARCHAR(40),
	[SpanishEducation] NVARCHAR(40),
	[FrenchEducation] NVARCHAR(40),
	[EnglishOccupation] NVARCHAR(100),
	[SpanishOccupation] NVARCHAR(100),
	[FrenchOccupation] NVARCHAR(100),
	[HouseOwnerFlag] NCHAR(1),
	[NumberCarsOwned] TINYINT,
	[AddressLine1] NVARCHAR(120),
	[AddressLine2] NVARCHAR(120),
	[Phone] NVARCHAR(20),
	[DateFirstPurchase] DATE,
	[CommuteDistance] NVARCHAR(15)
)WITH (
	CLUSTERED COLUMNSTORE INDEX,
	DISTRIBUTION = ROUND_ROBIN
);
2.1.2.6 Table DimDate
2.1.2.6.1 DimDate Properties
PropertyValue
TableDimDate
Technical name
Activatedtrue
Id
Schemadbo
Additional propertiesfalse
$ref
Comments
If not exist
Distributionround_robin
Persistenceregular
SQL pool indexingclustered columnstore index
Partition
CTAS query
Range for values
Boundary value
Remarks
2.1.2.6.2 DimDate Column
ColumnTypeReqKeyDescriptionComments
DateKeyinttruepk, dk
FullDateAlternateKeydatetrue
DayNumberOfWeektinyinttrue
EnglishDayNameOfWeeknvarchar(10)true
SpanishDayNameOfWeeknvarchar(10)true
FrenchDayNameOfWeeknvarchar(10)true
DayNumberOfMonthtinyinttrue
DayNumberOfYearsmallinttrue
WeekNumberOfYeartinyinttrue
EnglishMonthNamenvarchar(10)true
SpanishMonthNamenvarchar(10)true
FrenchMonthNamenvarchar(10)true
MonthNumberOfYeartinyinttrue
CalendarQuartertinyinttrue
CalendarYearsmallinttrue
CalendarSemestertinyinttrue
FiscalQuartertinyinttrue
FiscalYearsmallinttrue
FiscalSemestertinyinttrue
2.1.2.6.2.1 Column DateKey
2.1.2.6.2.1.1 DateKey properties
PropertyValue
Business Name DateKey
Technical name
Activated true
Id
Type numeric
Subtype int
Default
Default constraint name
Comments
Not null true
Masked with function
Identity
Primary key true
Foreign table
Foreign field
Relationship type
Relationship name
Cardinality
Unit
Min value
Excl min
Max value
Excl max
Multiple of
Divisible by
Pattern
Enum
Faker function
Sample 20040501
Remarks
2.1.2.6.2.2 Column FullDateAlternateKey
2.1.2.6.2.2.1 FullDateAlternateKey properties
PropertyValue
Business Name FullDateAlternateKey
Technical name
Activated true
Id
Type datetime
Subtype date
Comments
Not null true
Masked with function
Primary key
Unique
Pattern
Default
Default constraint name
Enum
Faker function
Sample 2004-05-01T00:00:00.000Z
Remarks
2.1.2.6.2.3 Column DayNumberOfWeek
2.1.2.6.2.3.1 DayNumberOfWeek properties
PropertyValue
Business Name DayNumberOfWeek
Technical name
Activated true
Id
Type numeric
Subtype tinyint
Default
Default constraint name
Comments
Not null true
Masked with function
Identity
Primary key
Unique
Foreign table
Foreign field
Relationship type
Relationship name
Cardinality
Unit
Min value
Excl min
Max value
Excl max
Multiple of
Divisible by
Pattern
Enum
Faker function
Sample 7
Remarks
2.1.2.6.2.4 Column EnglishDayNameOfWeek
2.1.2.6.2.4.1 EnglishDayNameOfWeek properties
PropertyValue
Business Name EnglishDayNameOfWeek
Technical name
Activated true
Id
Type char
Subtype nvarchar
Length 10
Default
Collate
Default constraint name
Comments
Not null true
Masked with function
Primary key
Unique
Foreign table
Foreign field
Relationship type
Relationship name
Cardinality
Min length
Pattern
Format
Enum
Faker function
Sample Saturday
Remarks
2.1.2.6.2.5 Column SpanishDayNameOfWeek
2.1.2.6.2.5.1 SpanishDayNameOfWeek properties
PropertyValue
Business Name SpanishDayNameOfWeek
Technical name
Activated true
Id
Type char
Subtype nvarchar
Length 10
Default
Collate
Default constraint name
Comments
Not null true
Masked with function
Primary key
Unique
Foreign table
Foreign field
Relationship type
Relationship name
Cardinality
Min length
Pattern
Format
Enum
Faker function
Sample Sábado
Remarks
2.1.2.6.2.6 Column FrenchDayNameOfWeek
2.1.2.6.2.6.1 FrenchDayNameOfWeek properties
PropertyValue
Business Name FrenchDayNameOfWeek
Technical name
Activated true
Id
Type char
Subtype nvarchar
Length 10
Default
Collate
Default constraint name
Comments
Not null true
Masked with function
Primary key
Unique
Foreign table
Foreign field
Relationship type
Relationship name
Cardinality
Min length
Pattern
Format
Enum
Faker function
Sample Samedi
Remarks
2.1.2.6.2.7 Column DayNumberOfMonth
2.1.2.6.2.7.1 DayNumberOfMonth properties
PropertyValue
Business Name DayNumberOfMonth
Technical name
Activated true
Id
Type numeric
Subtype tinyint
Default
Default constraint name
Comments
Not null true
Masked with function
Identity
Primary key
Unique
Foreign table
Foreign field
Relationship type
Relationship name
Cardinality
Unit
Min value
Excl min
Max value
Excl max
Multiple of
Divisible by
Pattern
Enum
Faker function
Sample 1
Remarks
2.1.2.6.2.8 Column DayNumberOfYear
2.1.2.6.2.8.1 DayNumberOfYear properties
PropertyValue
Business Name DayNumberOfYear
Technical name
Activated true
Id
Type numeric
Subtype smallint
Default
Default constraint name
Comments
Not null true
Masked with function
Identity
Primary key
Unique
Foreign table
Foreign field
Relationship type
Relationship name
Cardinality
Unit
Min value
Excl min
Max value
Excl max
Multiple of
Divisible by
Pattern
Enum
Faker function
Sample 122
Remarks
2.1.2.6.2.9 Column WeekNumberOfYear
2.1.2.6.2.9.1 WeekNumberOfYear properties
PropertyValue
Business Name WeekNumberOfYear
Technical name
Activated true
Id
Type numeric
Subtype tinyint
Default
Default constraint name
Comments
Not null true
Masked with function
Identity
Primary key
Unique
Foreign table
Foreign field
Relationship type
Relationship name
Cardinality
Unit
Min value
Excl min
Max value
Excl max
Multiple of
Divisible by
Pattern
Enum
Faker function
Sample 18
Remarks
2.1.2.6.2.10 Column EnglishMonthName
2.1.2.6.2.10.1 EnglishMonthName properties
PropertyValue
Business Name EnglishMonthName
Technical name
Activated true
Id
Type char
Subtype nvarchar
Length 10
Default
Collate
Default constraint name
Comments
Not null true
Masked with function
Primary key
Unique
Foreign table
Foreign field
Relationship type
Relationship name
Cardinality
Min length
Pattern
Format
Enum
Faker function
Sample May
Remarks
2.1.2.6.2.11 Column SpanishMonthName
2.1.2.6.2.11.1 SpanishMonthName properties
PropertyValue
Business Name SpanishMonthName
Technical name
Activated true
Id
Type char
Subtype nvarchar
Length 10
Default
Collate
Default constraint name
Comments
Not null true
Masked with function
Primary key
Unique
Foreign table
Foreign field
Relationship type
Relationship name
Cardinality
Min length
Pattern
Format
Enum
Faker function
Sample Mayo
Remarks
2.1.2.6.2.12 Column FrenchMonthName
2.1.2.6.2.12.1 FrenchMonthName properties
PropertyValue
Business Name FrenchMonthName
Technical name
Activated true
Id
Type char
Subtype nvarchar
Length 10
Default
Collate
Default constraint name
Comments
Not null true
Masked with function
Primary key
Unique
Foreign table
Foreign field
Relationship type
Relationship name
Cardinality
Min length
Pattern
Format
Enum
Faker function
Sample Mai
Remarks
2.1.2.6.2.13 Column MonthNumberOfYear
2.1.2.6.2.13.1 MonthNumberOfYear properties
PropertyValue
Business Name MonthNumberOfYear
Technical name
Activated true
Id
Type numeric
Subtype tinyint
Default
Default constraint name
Comments
Not null true
Masked with function
Identity
Primary key
Unique
Foreign table
Foreign field
Relationship type
Relationship name
Cardinality
Unit
Min value
Excl min
Max value
Excl max
Multiple of
Divisible by
Pattern
Enum
Faker function
Sample 5
Remarks
2.1.2.6.2.14 Column CalendarQuarter
2.1.2.6.2.14.1 CalendarQuarter properties
PropertyValue
Business Name CalendarQuarter
Technical name
Activated true
Id
Type numeric
Subtype tinyint
Default
Default constraint name
Comments
Not null true
Masked with function
Identity
Primary key
Unique
Foreign table
Foreign field
Relationship type
Relationship name
Cardinality
Unit
Min value
Excl min
Max value
Excl max
Multiple of
Divisible by
Pattern
Enum
Faker function
Sample 2
Remarks
2.1.2.6.2.15 Column CalendarYear
2.1.2.6.2.15.1 CalendarYear properties
PropertyValue
Business Name CalendarYear
Technical name
Activated true
Id
Type numeric
Subtype smallint
Default
Default constraint name
Comments
Not null true
Masked with function
Identity
Primary key
Unique
Foreign table
Foreign field
Relationship type
Relationship name
Cardinality
Unit
Min value
Excl min
Max value
Excl max
Multiple of
Divisible by
Pattern
Enum
Faker function
Sample 2004
Remarks
2.1.2.6.2.16 Column CalendarSemester
2.1.2.6.2.16.1 CalendarSemester properties
PropertyValue
Business Name CalendarSemester
Technical name
Activated true
Id
Type numeric
Subtype tinyint
Default
Default constraint name
Comments
Not null true
Masked with function
Identity
Primary key
Unique
Foreign table
Foreign field
Relationship type
Relationship name
Cardinality
Unit
Min value
Excl min
Max value
Excl max
Multiple of
Divisible by
Pattern
Enum
Faker function
Sample 1
Remarks
2.1.2.6.2.17 Column FiscalQuarter
2.1.2.6.2.17.1 FiscalQuarter properties
PropertyValue
Business Name FiscalQuarter
Technical name
Activated true
Id
Type numeric
Subtype tinyint
Default
Default constraint name
Comments
Not null true
Masked with function
Identity
Primary key
Unique
Foreign table
Foreign field
Relationship type
Relationship name
Cardinality
Unit
Min value
Excl min
Max value
Excl max
Multiple of
Divisible by
Pattern
Enum
Faker function
Sample 4
Remarks
2.1.2.6.2.18 Column FiscalYear
2.1.2.6.2.18.1 FiscalYear properties
PropertyValue
Business Name FiscalYear
Technical name
Activated true
Id
Type numeric
Subtype smallint
Default
Default constraint name
Comments
Not null true
Masked with function
Identity
Primary key
Unique
Foreign table
Foreign field
Relationship type
Relationship name
Cardinality
Unit
Min value
Excl min
Max value
Excl max
Multiple of
Divisible by
Pattern
Enum
Faker function
Sample 2004
Remarks
2.1.2.6.2.19 Column FiscalSemester
2.1.2.6.2.19.1 FiscalSemester properties
PropertyValue
Business Name FiscalSemester
Technical name
Activated true
Id
Type numeric
Subtype tinyint
Default
Default constraint name
Comments
Not null true
Masked with function
Identity
Primary key
Unique
Foreign table
Foreign field
Relationship type
Relationship name
Cardinality
Unit
Min value
Excl min
Max value
Excl max
Multiple of
Divisible by
Pattern
Enum
Faker function
Sample 2
Remarks
2.1.2.6.3 DimDate Composite keys
PropertyValue
Primary key
[1] Constraint name
Key
Unique key
[1] Constraint name
Key
Alternate key
2.1.2.6.4 DimDate JSON Schema
{
    "$schema": "http://json-schema.org/draft-04/schema#",
    "type": "object",
    "title": "DimDate",
    "additionalProperties": false,
    "properties": {
        "DateKey": {
            "type": "number"
        },
        "FullDateAlternateKey": {
            "type": "string",
            "maxLength": 24
        },
        "DayNumberOfWeek": {
            "type": "number"
        },
        "EnglishDayNameOfWeek": {
            "type": "string"
        },
        "SpanishDayNameOfWeek": {
            "type": "string"
        },
        "FrenchDayNameOfWeek": {
            "type": "string"
        },
        "DayNumberOfMonth": {
            "type": "number"
        },
        "DayNumberOfYear": {
            "type": "number"
        },
        "WeekNumberOfYear": {
            "type": "number"
        },
        "EnglishMonthName": {
            "type": "string"
        },
        "SpanishMonthName": {
            "type": "string"
        },
        "FrenchMonthName": {
            "type": "string"
        },
        "MonthNumberOfYear": {
            "type": "number"
        },
        "CalendarQuarter": {
            "type": "number"
        },
        "CalendarYear": {
            "type": "number"
        },
        "CalendarSemester": {
            "type": "number"
        },
        "FiscalQuarter": {
            "type": "number"
        },
        "FiscalYear": {
            "type": "number"
        },
        "FiscalSemester": {
            "type": "number"
        }
    },
    "required": [
        "DateKey",
        "FullDateAlternateKey",
        "DayNumberOfWeek",
        "EnglishDayNameOfWeek",
        "SpanishDayNameOfWeek",
        "FrenchDayNameOfWeek",
        "DayNumberOfMonth",
        "DayNumberOfYear",
        "WeekNumberOfYear",
        "EnglishMonthName",
        "SpanishMonthName",
        "FrenchMonthName",
        "MonthNumberOfYear",
        "CalendarQuarter",
        "CalendarYear",
        "CalendarSemester",
        "FiscalQuarter",
        "FiscalYear",
        "FiscalSemester"
    ]
}
2.1.2.6.5 DimDate JSON data
{
    "DateKey": 20040501,
    "FullDateAlternateKey": "2004-05-01T00:00:00.000Z",
    "DayNumberOfWeek": 7,
    "EnglishDayNameOfWeek": "Saturday",
    "SpanishDayNameOfWeek": "Sábado",
    "FrenchDayNameOfWeek": "Samedi",
    "DayNumberOfMonth": 1,
    "DayNumberOfYear": 122,
    "WeekNumberOfYear": 18,
    "EnglishMonthName": "May",
    "SpanishMonthName": "Mayo",
    "FrenchMonthName": "Mai",
    "MonthNumberOfYear": 5,
    "CalendarQuarter": 2,
    "CalendarYear": 2004,
    "CalendarSemester": 1,
    "FiscalQuarter": 4,
    "FiscalYear": 2004,
    "FiscalSemester": 2
}
2.1.2.6.6 DimDate Target Script
IF NOT EXISTS (SELECT * FROM sys.databases WHERE name = N'[hacksqlpool]')
begin
	CREATE DATABASE [hacksqlpool];
	USE [hacksqlpool];
end;

CREATE SCHEMA [dbo];

CREATE TABLE [dbo].[DimDate] (
	[DateKey] INT PRIMARY KEY NONCLUSTERED NOT ENFORCED NOT NULL,
	[FullDateAlternateKey] DATE NOT NULL,
	[DayNumberOfWeek] TINYINT NOT NULL,
	[EnglishDayNameOfWeek] NVARCHAR(10) NOT NULL,
	[SpanishDayNameOfWeek] NVARCHAR(10) NOT NULL,
	[FrenchDayNameOfWeek] NVARCHAR(10) NOT NULL,
	[DayNumberOfMonth] TINYINT NOT NULL,
	[DayNumberOfYear] SMALLINT NOT NULL,
	[WeekNumberOfYear] TINYINT NOT NULL,
	[EnglishMonthName] NVARCHAR(10) NOT NULL,
	[SpanishMonthName] NVARCHAR(10) NOT NULL,
	[FrenchMonthName] NVARCHAR(10) NOT NULL,
	[MonthNumberOfYear] TINYINT NOT NULL,
	[CalendarQuarter] TINYINT NOT NULL,
	[CalendarYear] SMALLINT NOT NULL,
	[CalendarSemester] TINYINT NOT NULL,
	[FiscalQuarter] TINYINT NOT NULL,
	[FiscalYear] SMALLINT NOT NULL,
	[FiscalSemester] TINYINT NOT NULL
)WITH (
	CLUSTERED COLUMNSTORE INDEX,
	DISTRIBUTION = ROUND_ROBIN
);
2.1.2.7 Table DimDepartmentGroup
2.1.2.7.1 DimDepartmentGroup Properties
PropertyValue
TableDimDepartmentGroup
Technical name
Activatedtrue
Id
Schemadbo
Additional propertiesfalse
$ref
Comments
If not exist
Distributionround_robin
Persistenceregular
SQL pool indexingclustered columnstore index
Partition
CTAS query
Range for values
Boundary value
Remarks
2.1.2.7.2 DimDepartmentGroup Column
ColumnTypeReqKeyDescriptionComments
DepartmentGroupKeyinttruepk, dk
ParentDepartmentGroupKeyintfalsefk
DepartmentGroupNamenvarchar(50)false
2.1.2.7.2.1 Column DepartmentGroupKey
2.1.2.7.2.1.1 DepartmentGroupKey properties
PropertyValue
Business Name DepartmentGroupKey
Technical name
Activated true
Id
Type numeric
Subtype int
Default
Default constraint name
Comments
Not null true
Masked with function
Identity
Primary key true
Foreign table
Foreign field
Relationship type
Relationship name
Cardinality
Unit
Min value
Excl min
Max value
Excl max
Multiple of
Divisible by
Pattern
Enum
Faker function
Sample 4
Remarks
2.1.2.7.2.2 Column ParentDepartmentGroupKey
2.1.2.7.2.2.1 ParentDepartmentGroupKey properties
PropertyValue
Business Name ParentDepartmentGroupKey
Technical name
Activated true
Id
Type numeric
Subtype int
Default
Default constraint name
Comments
Not null
Masked with function
Identity
Primary key
Unique
Foreign table DimDepartmentGroup
Foreign field DepartmentGroupKey
Relationship type Foreign Key
Relationship name fk DimDepartmentGroup.DepartmentGroupKey to DimDepartmentGroup.ParentDepartmentGroupKey
Cardinality 0..n
Unit
Min value
Excl min
Max value
Excl max
Multiple of
Divisible by
Pattern
Enum
Faker function
Sample 1
Remarks
2.1.2.7.2.3 Column DepartmentGroupName
2.1.2.7.2.3.1 DepartmentGroupName properties
PropertyValue
Business Name DepartmentGroupName
Technical name
Activated true
Id
Type char
Subtype nvarchar
Length 50
Default
Collate
Default constraint name
Comments
Not null
Masked with function
Primary key
Unique
Foreign table
Foreign field
Relationship type
Relationship name
Cardinality
Min length
Pattern
Format
Enum
Faker function
Sample Manufacturing
Remarks
2.1.2.7.3 DimDepartmentGroup Composite keys
PropertyValue
Primary key
[1] Constraint name
Key
Unique key
[1] Constraint name
Key
Alternate key
2.1.2.7.4 DimDepartmentGroup JSON Schema
{
    "$schema": "http://json-schema.org/draft-04/schema#",
    "type": "object",
    "title": "DimDepartmentGroup",
    "additionalProperties": false,
    "properties": {
        "DepartmentGroupKey": {
            "type": "number"
        },
        "ParentDepartmentGroupKey": {
            "type": "number"
        },
        "DepartmentGroupName": {
            "type": "string"
        }
    },
    "required": [
        "DepartmentGroupKey"
    ]
}
2.1.2.7.5 DimDepartmentGroup JSON data
{
    "DepartmentGroupKey": 4,
    "ParentDepartmentGroupKey": 1,
    "DepartmentGroupName": "Manufacturing"
}
2.1.2.7.6 DimDepartmentGroup Target Script
IF NOT EXISTS (SELECT * FROM sys.databases WHERE name = N'[hacksqlpool]')
begin
	CREATE DATABASE [hacksqlpool];
	USE [hacksqlpool];
end;

CREATE SCHEMA [dbo];

CREATE TABLE [dbo].[DimDepartmentGroup] (
	[DepartmentGroupKey] INT PRIMARY KEY NONCLUSTERED NOT ENFORCED NOT NULL,
	[ParentDepartmentGroupKey] INT,
	[DepartmentGroupName] NVARCHAR(50)
)WITH (
	CLUSTERED COLUMNSTORE INDEX,
	DISTRIBUTION = ROUND_ROBIN
);
2.1.2.8 Table DimEmployee
2.1.2.8.1 DimEmployee Properties
PropertyValue
TableDimEmployee
Technical name
Activatedtrue
Id
Schemadbo
Additional propertiesfalse
$ref
Comments
If not exist
Distributionround_robin
Persistenceregular
SQL pool indexingclustered columnstore index
Partition
CTAS query
Range for values
Boundary value
Remarks
2.1.2.8.2 DimEmployee Column
ColumnTypeReqKeyDescriptionComments
EmployeeKeyinttruepk, dk
ParentEmployeeKeyintfalsefk
EmployeeNationalIDAlternateKeynvarchar(15)false
ParentEmployeeNationalIDAlternateKeynvarchar(15)false
SalesTerritoryKeyintfalsefk
FirstNamenvarchar(50)true
LastNamenvarchar(50)true
MiddleNamenvarchar(50)false
NameStylebittrue
Titlenvarchar(50)false
HireDatedatefalse
BirthDatedatefalse
LoginIDnvarchar(256)false
EmailAddressnvarchar(50)false
Phonenvarchar(25)false
MaritalStatusnchar(1)false
EmergencyContactNamenvarchar(50)false
EmergencyContactPhonenvarchar(25)false
SalariedFlagbitfalse
Gendernchar(1)false
PayFrequencytinyintfalse
BaseRatemoneyfalse
VacationHourssmallintfalse
SickLeaveHourssmallintfalse
CurrentFlagbittrue
SalesPersonFlagbittrue
DepartmentNamenvarchar(50)false
StartDatedatefalse
EndDatedatefalse
Statusnvarchar(50)false
2.1.2.8.2.1 Column EmployeeKey
2.1.2.8.2.1.1 EmployeeKey properties
PropertyValue
Business Name EmployeeKey
Technical name
Activated true
Id
Type numeric
Subtype int
Default
Default constraint name
Comments
Not null true
Masked with function
Identity
Primary key true
Foreign table
Foreign field
Relationship type
Relationship name
Cardinality
Unit
Min value
Excl min
Max value
Excl max
Multiple of
Divisible by
Pattern
Enum
Faker function
Sample 274
Remarks
2.1.2.8.2.2 Column ParentEmployeeKey
2.1.2.8.2.2.1 ParentEmployeeKey properties
PropertyValue
Business Name ParentEmployeeKey
Technical name
Activated true
Id
Type numeric
Subtype int
Default
Default constraint name
Comments
Not null
Masked with function
Identity
Primary key
Unique
Foreign table DimEmployee
Foreign field EmployeeKey
Relationship type Foreign Key
Relationship name fk DimEmployee.ParentEmployeeKey to DimEmployee.EmployeeKey
Cardinality 1..n
Unit
Min value
Excl min
Max value
Excl max
Multiple of
Divisible by
Pattern
Enum
Faker function
Sample 3
Remarks
2.1.2.8.2.3 Column EmployeeNationalIDAlternateKey
2.1.2.8.2.3.1 EmployeeNationalIDAlternateKey properties
PropertyValue
Business Name EmployeeNationalIDAlternateKey
Technical name
Activated true
Id
Type char
Subtype nvarchar
Length 15
Default
Collate
Default constraint name
Comments
Not null
Masked with function
Primary key
Unique
Foreign table
Foreign field
Relationship type
Relationship name
Cardinality
Min length
Pattern
Format
Enum
Faker function
Sample 56920285
Remarks
2.1.2.8.2.4 Column ParentEmployeeNationalIDAlternateKey
2.1.2.8.2.4.1 ParentEmployeeNationalIDAlternateKey properties
PropertyValue
Business Name ParentEmployeeNationalIDAlternateKey
Technical name
Activated true
Id
Type char
Subtype nvarchar
Length 15
Default
Collate
Default constraint name
Comments
Not null
Masked with function
Primary key
Unique
Foreign table
Foreign field
Relationship type
Relationship name
Cardinality
Min length
Pattern
Format
Enum
Faker function
Sample
Remarks
2.1.2.8.2.5 Column SalesTerritoryKey
2.1.2.8.2.5.1 SalesTerritoryKey properties
PropertyValue
Business Name SalesTerritoryKey
Technical name
Activated true
Id
Type numeric
Subtype int
Default
Default constraint name
Comments
Not null
Masked with function
Identity
Primary key
Unique
Foreign table DimSalesTerritory
Foreign field SalesTerritoryKey
Relationship type Foreign Key
Relationship name fk DimEmployee.SalesTerritoryKey to DimSalesTerritory.SalesTerritoryKey
Cardinality 1..n
Unit
Min value
Excl min
Max value
Excl max
Multiple of
Divisible by
Pattern
Enum
Faker function
Sample 11
Remarks
2.1.2.8.2.6 Column FirstName
2.1.2.8.2.6.1 FirstName properties
PropertyValue
Business Name FirstName
Technical name
Activated true
Id
Type char
Subtype nvarchar
Length 50
Default
Collate
Default constraint name
Comments
Not null true
Masked with function
Primary key
Unique
Foreign table
Foreign field
Relationship type
Relationship name
Cardinality
Min length
Pattern
Format
Enum
Faker function
Sample Sharon
Remarks
2.1.2.8.2.7 Column LastName
2.1.2.8.2.7.1 LastName properties
PropertyValue
Business Name LastName
Technical name
Activated true
Id
Type char
Subtype nvarchar
Length 50
Default
Collate
Default constraint name
Comments
Not null true
Masked with function
Primary key
Unique
Foreign table
Foreign field
Relationship type
Relationship name
Cardinality
Min length
Pattern
Format
Enum
Faker function
Sample Salavaria
Remarks
2.1.2.8.2.8 Column MiddleName
2.1.2.8.2.8.1 MiddleName properties
PropertyValue
Business Name MiddleName
Technical name
Activated true
Id
Type char
Subtype nvarchar
Length 50
Default
Collate
Default constraint name
Comments
Not null
Masked with function
Primary key
Unique
Foreign table
Foreign field
Relationship type
Relationship name
Cardinality
Min length
Pattern
Format
Enum
Faker function
Sample B
Remarks
2.1.2.8.2.9 Column NameStyle
2.1.2.8.2.9.1 NameStyle properties
PropertyValue
Business Name NameStyle
Technical name
Activated true
Id
Type numeric
Subtype bit
Default
Default constraint name
Comments
Not null true
Masked with function
Identity
Primary key
Unique
Foreign table
Foreign field
Relationship type
Relationship name
Cardinality
Unit
Min value
Excl min
Max value
Excl max
Multiple of
Divisible by
Pattern
Enum
Faker function
Sample false
Remarks
2.1.2.8.2.10 Column Title
2.1.2.8.2.10.1 Title properties
PropertyValue
Business Name Title
Technical name
Activated true
Id
Type char
Subtype nvarchar
Length 50
Default
Collate
Default constraint name
Comments
Not null
Masked with function
Primary key
Unique
Foreign table
Foreign field
Relationship type
Relationship name
Cardinality
Min length
Pattern
Format
Enum
Faker function
Sample Design Engineer
Remarks
2.1.2.8.2.11 Column HireDate
2.1.2.8.2.11.1 HireDate properties
PropertyValue
Business Name HireDate
Technical name
Activated true
Id
Type datetime
Subtype date
Comments
Not null
Masked with function
Primary key
Unique
Pattern
Default
Default constraint name
Enum
Faker function
Sample 2001-02-18T00:00:00.000Z
Remarks
2.1.2.8.2.12 Column BirthDate
2.1.2.8.2.12.1 BirthDate properties
PropertyValue
Business Name BirthDate
Technical name
Activated true
Id
Type datetime
Subtype date
Comments
Not null
Masked with function
Primary key
Unique
Pattern
Default
Default constraint name
Enum
Faker function
Sample 1951-06-03T00:00:00.000Z
Remarks
2.1.2.8.2.13 Column LoginID
2.1.2.8.2.13.1 LoginID properties
PropertyValue
Business Name LoginID
Technical name
Activated true
Id
Type char
Subtype nvarchar
Length 256
Default
Collate
Default constraint name
Comments
Not null
Masked with function
Primary key
Unique
Foreign table
Foreign field
Relationship type
Relationship name
Cardinality
Min length
Pattern
Format
Enum
Faker function
Sample adventure-works\sharon0
Remarks
2.1.2.8.2.14 Column EmailAddress
2.1.2.8.2.14.1 EmailAddress properties
PropertyValue
Business Name EmailAddress
Technical name
Activated true
Id
Type char
Subtype nvarchar
Length 50
Default
Collate
Default constraint name
Comments
Not null
Masked with function
Primary key
Unique
Foreign table
Foreign field
Relationship type
Relationship name
Cardinality
Min length
Pattern
Format
Enum
Faker function
Sample sharon0@adventure-works.com
Remarks
2.1.2.8.2.15 Column Phone
2.1.2.8.2.15.1 Phone properties
PropertyValue
Business Name Phone
Technical name
Activated true
Id
Type char
Subtype nvarchar
Length 25
Default
Collate
Default constraint name
Comments
Not null
Masked with function
Primary key
Unique
Foreign table
Foreign field
Relationship type
Relationship name
Cardinality
Min length
Pattern
Format
Enum
Faker function
Sample 970-555-0138
Remarks
2.1.2.8.2.16 Column MaritalStatus
2.1.2.8.2.16.1 MaritalStatus properties
PropertyValue
Business Name MaritalStatus
Technical name
Activated true
Id
Type char
Subtype nchar
Length 1
Default
Collate
Default constraint name
Comments
Not null
Masked with function
Primary key
Unique
Foreign table
Foreign field
Relationship type
Relationship name
Cardinality
Min length
Pattern
Format
Enum
Faker function
Sample M
Remarks
2.1.2.8.2.17 Column EmergencyContactName
2.1.2.8.2.17.1 EmergencyContactName properties
PropertyValue
Business Name EmergencyContactName
Technical name
Activated true
Id
Type char
Subtype nvarchar
Length 50
Default
Collate
Default constraint name
Comments
Not null
Masked with function
Primary key
Unique
Foreign table
Foreign field
Relationship type
Relationship name
Cardinality
Min length
Pattern
Format
Enum
Faker function
Sample Sharon Salavaria
Remarks
2.1.2.8.2.18 Column EmergencyContactPhone
2.1.2.8.2.18.1 EmergencyContactPhone properties
PropertyValue
Business Name EmergencyContactPhone
Technical name
Activated true
Id
Type char
Subtype nvarchar
Length 25
Default
Collate
Default constraint name
Comments
Not null
Masked with function
Primary key
Unique
Foreign table
Foreign field
Relationship type
Relationship name
Cardinality
Min length
Pattern
Format
Enum
Faker function
Sample 970-555-0138
Remarks
2.1.2.8.2.19 Column SalariedFlag
2.1.2.8.2.19.1 SalariedFlag properties
PropertyValue
Business Name SalariedFlag
Technical name
Activated true
Id
Type numeric
Subtype bit
Default
Default constraint name
Comments
Not null
Masked with function
Identity
Primary key
Unique
Foreign table
Foreign field
Relationship type
Relationship name
Cardinality
Unit
Min value
Excl min
Max value
Excl max
Multiple of
Divisible by
Pattern
Enum
Faker function
Sample true
Remarks
2.1.2.8.2.20 Column Gender
2.1.2.8.2.20.1 Gender properties
PropertyValue
Business Name Gender
Technical name
Activated true
Id
Type char
Subtype nchar
Length 1
Default
Collate
Default constraint name
Comments
Not null
Masked with function
Primary key
Unique
Foreign table
Foreign field
Relationship type
Relationship name
Cardinality
Min length
Pattern
Format
Enum
Faker function
Sample F
Remarks
2.1.2.8.2.21 Column PayFrequency
2.1.2.8.2.21.1 PayFrequency properties
PropertyValue
Business Name PayFrequency
Technical name
Activated true
Id
Type numeric
Subtype tinyint
Default
Default constraint name
Comments
Not null
Masked with function
Identity
Primary key
Unique
Foreign table
Foreign field
Relationship type
Relationship name
Cardinality
Unit
Min value
Excl min
Max value
Excl max
Multiple of
Divisible by
Pattern
Enum
Faker function
Sample 2
Remarks
2.1.2.8.2.22 Column BaseRate
2.1.2.8.2.22.1 BaseRate properties
PropertyValue
Business Name BaseRate
Technical name
Activated true
Id
Type numeric
Subtype money
Default
Default constraint name
Comments
Not null
Masked with function
Identity
Primary key
Unique
Foreign table
Foreign field
Relationship type
Relationship name
Cardinality
Unit
Min value
Excl min
Max value
Excl max
Multiple of
Divisible by
Pattern
Enum
Faker function
Sample 32.6923
Remarks
2.1.2.8.2.23 Column VacationHours
2.1.2.8.2.23.1 VacationHours properties
PropertyValue
Business Name VacationHours
Technical name
Activated true
Id
Type numeric
Subtype smallint
Default
Default constraint name
Comments
Not null
Masked with function
Identity
Primary key
Unique
Foreign table
Foreign field
Relationship type
Relationship name
Cardinality
Unit
Min value
Excl min
Max value
Excl max
Multiple of
Divisible by
Pattern
Enum
Faker function
Sample 4
Remarks
2.1.2.8.2.24 Column SickLeaveHours
2.1.2.8.2.24.1 SickLeaveHours properties
PropertyValue
Business Name SickLeaveHours
Technical name
Activated true
Id
Type numeric
Subtype smallint
Default
Default constraint name
Comments
Not null
Masked with function
Identity
Primary key
Unique
Foreign table
Foreign field
Relationship type
Relationship name
Cardinality
Unit
Min value
Excl min
Max value
Excl max
Multiple of
Divisible by
Pattern
Enum
Faker function
Sample 22
Remarks
2.1.2.8.2.25 Column CurrentFlag
2.1.2.8.2.25.1 CurrentFlag properties
PropertyValue
Business Name CurrentFlag
Technical name
Activated true
Id
Type numeric
Subtype bit
Default
Default constraint name
Comments
Not null true
Masked with function
Identity
Primary key
Unique
Foreign table
Foreign field
Relationship type
Relationship name
Cardinality
Unit
Min value
Excl min
Max value
Excl max
Multiple of
Divisible by
Pattern
Enum
Faker function
Sample true
Remarks
2.1.2.8.2.26 Column SalesPersonFlag
2.1.2.8.2.26.1 SalesPersonFlag properties
PropertyValue
Business Name SalesPersonFlag
Technical name
Activated true
Id
Type numeric
Subtype bit
Default
Default constraint name
Comments
Not null true
Masked with function
Identity
Primary key
Unique
Foreign table
Foreign field
Relationship type
Relationship name
Cardinality
Unit
Min value
Excl min
Max value
Excl max
Multiple of
Divisible by
Pattern
Enum
Faker function
Sample false
Remarks
2.1.2.8.2.27 Column DepartmentName
2.1.2.8.2.27.1 DepartmentName properties
PropertyValue
Business Name DepartmentName
Technical name
Activated true
Id
Type char
Subtype nvarchar
Length 50
Default
Collate
Default constraint name
Comments
Not null
Masked with function
Primary key
Unique
Foreign table
Foreign field
Relationship type
Relationship name
Cardinality
Min length
Pattern
Format
Enum
Faker function
Sample Engineering
Remarks
2.1.2.8.2.28 Column StartDate
2.1.2.8.2.28.1 StartDate properties
PropertyValue
Business Name StartDate
Technical name
Activated true
Id
Type datetime
Subtype date
Comments
Not null
Masked with function
Primary key
Unique
Pattern
Default
Default constraint name
Enum
Faker function
Sample 2001-02-18T00:00:00.000Z
Remarks
2.1.2.8.2.29 Column EndDate
2.1.2.8.2.29.1 EndDate properties
PropertyValue
Business Name EndDate
Technical name
Activated true
Id
Type datetime
Subtype date
Comments
Not null
Masked with function
Primary key
Unique
Pattern
Default
Default constraint name
Enum
Faker function
Sample 2001-08-30T00:00:00.000Z
Remarks
2.1.2.8.2.30 Column Status
2.1.2.8.2.30.1 Status properties
PropertyValue
Business Name Status
Technical name
Activated true
Id
Type char
Subtype nvarchar
Length 50
Default
Collate
Default constraint name
Comments
Not null
Masked with function
Primary key
Unique
Foreign table
Foreign field
Relationship type
Relationship name
Cardinality
Min length
Pattern
Format
Enum
Faker function
Sample Current
Remarks
2.1.2.8.3 DimEmployee Composite keys
PropertyValue
Primary key
[1] Constraint name
Key
Unique key
[1] Constraint name
Key
Alternate key
2.1.2.8.4 DimEmployee JSON Schema
{
    "$schema": "http://json-schema.org/draft-04/schema#",
    "type": "object",
    "title": "DimEmployee",
    "additionalProperties": false,
    "properties": {
        "EmployeeKey": {
            "type": "number"
        },
        "ParentEmployeeKey": {
            "type": "number"
        },
        "EmployeeNationalIDAlternateKey": {
            "type": "string"
        },
        "ParentEmployeeNationalIDAlternateKey": {
            "type": "string"
        },
        "SalesTerritoryKey": {
            "type": "number"
        },
        "FirstName": {
            "type": "string"
        },
        "LastName": {
            "type": "string"
        },
        "MiddleName": {
            "type": "string"
        },
        "NameStyle": {
            "type": "number"
        },
        "Title": {
            "type": "string"
        },
        "HireDate": {
            "type": "string",
            "maxLength": 24
        },
        "BirthDate": {
            "type": "string",
            "maxLength": 24
        },
        "LoginID": {
            "type": "string"
        },
        "EmailAddress": {
            "type": "string"
        },
        "Phone": {
            "type": "string"
        },
        "MaritalStatus": {
            "type": "string"
        },
        "EmergencyContactName": {
            "type": "string"
        },
        "EmergencyContactPhone": {
            "type": "string"
        },
        "SalariedFlag": {
            "type": "number"
        },
        "Gender": {
            "type": "string"
        },
        "PayFrequency": {
            "type": "number"
        },
        "BaseRate": {
            "type": "number"
        },
        "VacationHours": {
            "type": "number"
        },
        "SickLeaveHours": {
            "type": "number"
        },
        "CurrentFlag": {
            "type": "number"
        },
        "SalesPersonFlag": {
            "type": "number"
        },
        "DepartmentName": {
            "type": "string"
        },
        "StartDate": {
            "type": "string",
            "maxLength": 24
        },
        "EndDate": {
            "type": "string",
            "maxLength": 24
        },
        "Status": {
            "type": "string"
        }
    },
    "required": [
        "EmployeeKey",
        "FirstName",
        "LastName",
        "NameStyle",
        "CurrentFlag",
        "SalesPersonFlag"
    ]
}
2.1.2.8.5 DimEmployee JSON data
{
    "EmployeeKey": 274,
    "ParentEmployeeKey": 3,
    "EmployeeNationalIDAlternateKey": "56920285",
    "ParentEmployeeNationalIDAlternateKey": "Lorem",
    "SalesTerritoryKey": 11,
    "FirstName": "Sharon",
    "LastName": "Salavaria",
    "MiddleName": "B",
    "NameStyle": false,
    "Title": "Design Engineer",
    "HireDate": "2001-02-18T00:00:00.000Z",
    "BirthDate": "1951-06-03T00:00:00.000Z",
    "LoginID": "adventure-works\\sharon0",
    "EmailAddress": "sharon0@adventure-works.com",
    "Phone": "970-555-0138",
    "MaritalStatus": "M",
    "EmergencyContactName": "Sharon Salavaria",
    "EmergencyContactPhone": "970-555-0138",
    "SalariedFlag": true,
    "Gender": "F",
    "PayFrequency": 2,
    "BaseRate": 32.6923,
    "VacationHours": 4,
    "SickLeaveHours": 22,
    "CurrentFlag": true,
    "SalesPersonFlag": false,
    "DepartmentName": "Engineering",
    "StartDate": "2001-02-18T00:00:00.000Z",
    "EndDate": "2001-08-30T00:00:00.000Z",
    "Status": "Current"
}
2.1.2.8.6 DimEmployee Target Script
IF NOT EXISTS (SELECT * FROM sys.databases WHERE name = N'[hacksqlpool]')
begin
	CREATE DATABASE [hacksqlpool];
	USE [hacksqlpool];
end;

CREATE SCHEMA [dbo];

CREATE TABLE [dbo].[DimEmployee] (
	[EmployeeKey] INT PRIMARY KEY NONCLUSTERED NOT ENFORCED NOT NULL,
	[ParentEmployeeKey] INT,
	[EmployeeNationalIDAlternateKey] NVARCHAR(15),
	[ParentEmployeeNationalIDAlternateKey] NVARCHAR(15),
	[SalesTerritoryKey] INT,
	[FirstName] NVARCHAR(50) NOT NULL,
	[LastName] NVARCHAR(50) NOT NULL,
	[MiddleName] NVARCHAR(50),
	[NameStyle] BIT NOT NULL,
	[Title] NVARCHAR(50),
	[HireDate] DATE,
	[BirthDate] DATE,
	[LoginID] NVARCHAR(256),
	[EmailAddress] NVARCHAR(50),
	[Phone] NVARCHAR(25),
	[MaritalStatus] NCHAR(1),
	[EmergencyContactName] NVARCHAR(50),
	[EmergencyContactPhone] NVARCHAR(25),
	[SalariedFlag] BIT,
	[Gender] NCHAR(1),
	[PayFrequency] TINYINT,
	[BaseRate] MONEY,
	[VacationHours] SMALLINT,
	[SickLeaveHours] SMALLINT,
	[CurrentFlag] BIT NOT NULL,
	[SalesPersonFlag] BIT NOT NULL,
	[DepartmentName] NVARCHAR(50),
	[StartDate] DATE,
	[EndDate] DATE,
	[Status] NVARCHAR(50)
)WITH (
	CLUSTERED COLUMNSTORE INDEX,
	DISTRIBUTION = ROUND_ROBIN
);
2.1.2.9 Table DimGeography
2.1.2.9.1 DimGeography Properties
PropertyValue
TableDimGeography
Technical name
Activatedtrue
Id
Schemadbo
Additional propertiesfalse
$ref
Comments
If not exist
Distributionround_robin
Persistenceregular
SQL pool indexingclustered columnstore index
Partition
CTAS query
Range for values
Boundary value
Remarks
2.1.2.9.2 DimGeography Column
ColumnTypeReqKeyDescriptionComments
GeographyKeyinttruepk, dk
Citynvarchar(30)false
StateProvinceCodenvarchar(3)false
StateProvinceNamenvarchar(50)false
CountryRegionCodenvarchar(3)false
EnglishCountryRegionNamenvarchar(50)false
SpanishCountryRegionNamenvarchar(50)false
FrenchCountryRegionNamenvarchar(50)false
PostalCodenvarchar(15)false
SalesTerritoryKeyintfalsefk
2.1.2.9.2.1 Column GeographyKey
2.1.2.9.2.1.1 GeographyKey properties
PropertyValue
Business Name GeographyKey
Technical name
Activated true
Id
Type numeric
Subtype int
Default
Default constraint name
Comments
Not null true
Masked with function
Identity
Primary key true
Foreign table
Foreign field
Relationship type
Relationship name
Cardinality
Unit
Min value
Excl min
Max value
Excl max
Multiple of
Divisible by
Pattern
Enum
Faker function
Sample 654
Remarks
2.1.2.9.2.2 Column City
2.1.2.9.2.2.1 City properties
PropertyValue
Business Name City
Technical name
Activated true
Id
Type char
Subtype nvarchar
Length 30
Default
Collate
Default constraint name
Comments
Not null
Masked with function
Primary key
Unique
Foreign table
Foreign field
Relationship type
Relationship name
Cardinality
Min length
Pattern
Format
Enum
Faker function
Sample Cheyenne
Remarks
2.1.2.9.2.3 Column StateProvinceCode
2.1.2.9.2.3.1 StateProvinceCode properties
PropertyValue
Business Name StateProvinceCode
Technical name
Activated true
Id
Type char
Subtype nvarchar
Length 3
Default
Collate
Default constraint name
Comments
Not null
Masked with function
Primary key
Unique
Foreign table
Foreign field
Relationship type
Relationship name
Cardinality
Min length
Pattern
Format
Enum
Faker function
Sample WY
Remarks
2.1.2.9.2.4 Column StateProvinceName
2.1.2.9.2.4.1 StateProvinceName properties
PropertyValue
Business Name StateProvinceName
Technical name
Activated true
Id
Type char
Subtype nvarchar
Length 50
Default
Collate
Default constraint name
Comments
Not null
Masked with function
Primary key
Unique
Foreign table
Foreign field
Relationship type
Relationship name
Cardinality
Min length
Pattern
Format
Enum
Faker function
Sample Wyoming
Remarks
2.1.2.9.2.5 Column CountryRegionCode
2.1.2.9.2.5.1 CountryRegionCode properties
PropertyValue
Business Name CountryRegionCode
Technical name
Activated true
Id
Type char
Subtype nvarchar
Length 3
Default
Collate
Default constraint name
Comments
Not null
Masked with function
Primary key
Unique
Foreign table
Foreign field
Relationship type
Relationship name
Cardinality
Min length
Pattern
Format
Enum
Faker function
Sample US
Remarks
2.1.2.9.2.6 Column EnglishCountryRegionName
2.1.2.9.2.6.1 EnglishCountryRegionName properties
PropertyValue
Business Name EnglishCountryRegionName
Technical name
Activated true
Id
Type char
Subtype nvarchar
Length 50
Default
Collate
Default constraint name
Comments
Not null
Masked with function
Primary key
Unique
Foreign table
Foreign field
Relationship type
Relationship name
Cardinality
Min length
Pattern
Format
Enum
Faker function
Sample United States
Remarks
2.1.2.9.2.7 Column SpanishCountryRegionName
2.1.2.9.2.7.1 SpanishCountryRegionName properties
PropertyValue
Business Name SpanishCountryRegionName
Technical name
Activated true
Id
Type char
Subtype nvarchar
Length 50
Default
Collate
Default constraint name
Comments
Not null
Masked with function
Primary key
Unique
Foreign table
Foreign field
Relationship type
Relationship name
Cardinality
Min length
Pattern
Format
Enum
Faker function
Sample Estados Unidos
Remarks
2.1.2.9.2.8 Column FrenchCountryRegionName
2.1.2.9.2.8.1 FrenchCountryRegionName properties
PropertyValue
Business Name FrenchCountryRegionName
Technical name
Activated true
Id
Type char
Subtype nvarchar
Length 50
Default
Collate
Default constraint name
Comments
Not null
Masked with function
Primary key
Unique
Foreign table
Foreign field
Relationship type
Relationship name
Cardinality
Min length
Pattern
Format
Enum
Faker function
Sample États-Unis
Remarks
2.1.2.9.2.9 Column PostalCode
2.1.2.9.2.9.1 PostalCode properties
PropertyValue
Business Name PostalCode
Technical name
Activated true
Id
Type char
Subtype nvarchar
Length 15
Default
Collate
Default constraint name
Comments
Not null
Masked with function
Primary key
Unique
Foreign table
Foreign field
Relationship type
Relationship name
Cardinality
Min length
Pattern
Format
Enum
Faker function
Sample 82001
Remarks
2.1.2.9.2.10 Column SalesTerritoryKey
2.1.2.9.2.10.1 SalesTerritoryKey properties
PropertyValue
Business Name SalesTerritoryKey
Technical name
Activated true
Id
Type numeric
Subtype int
Default
Default constraint name
Comments
Not null
Masked with function
Identity
Primary key
Unique
Foreign table DimSalesTerritory
Foreign field SalesTerritoryKey
Relationship type Foreign Key
Relationship name fk DimGeography.SalesTerritoryKey to DimSalesTerritory.SalesTerritoryKey
Cardinality 1..n
Unit
Min value
Excl min
Max value
Excl max
Multiple of
Divisible by
Pattern
Enum
Faker function
Sample 1
Remarks
2.1.2.9.3 DimGeography Composite keys
PropertyValue
Primary key
[1] Constraint name
Key
Unique key
[1] Constraint name
Key
Alternate key
2.1.2.9.4 DimGeography JSON Schema
{
    "$schema": "http://json-schema.org/draft-04/schema#",
    "type": "object",
    "title": "DimGeography",
    "additionalProperties": false,
    "properties": {
        "GeographyKey": {
            "type": "number"
        },
        "City": {
            "type": "string"
        },
        "StateProvinceCode": {
            "type": "string"
        },
        "StateProvinceName": {
            "type": "string"
        },
        "CountryRegionCode": {
            "type": "string"
        },
        "EnglishCountryRegionName": {
            "type": "string"
        },
        "SpanishCountryRegionName": {
            "type": "string"
        },
        "FrenchCountryRegionName": {
            "type": "string"
        },
        "PostalCode": {
            "type": "string"
        },
        "SalesTerritoryKey": {
            "type": "number"
        }
    },
    "required": [
        "GeographyKey"
    ]
}
2.1.2.9.5 DimGeography JSON data
{
    "GeographyKey": 654,
    "City": "Cheyenne",
    "StateProvinceCode": "WY",
    "StateProvinceName": "Wyoming",
    "CountryRegionCode": "US",
    "EnglishCountryRegionName": "United States",
    "SpanishCountryRegionName": "Estados Unidos",
    "FrenchCountryRegionName": "États-Unis",
    "PostalCode": "82001",
    "SalesTerritoryKey": 1
}
2.1.2.9.6 DimGeography Target Script
IF NOT EXISTS (SELECT * FROM sys.databases WHERE name = N'[hacksqlpool]')
begin
	CREATE DATABASE [hacksqlpool];
	USE [hacksqlpool];
end;

CREATE SCHEMA [dbo];

CREATE TABLE [dbo].[DimGeography] (
	[GeographyKey] INT PRIMARY KEY NONCLUSTERED NOT ENFORCED NOT NULL,
	[City] NVARCHAR(30),
	[StateProvinceCode] NVARCHAR(3),
	[StateProvinceName] NVARCHAR(50),
	[CountryRegionCode] NVARCHAR(3),
	[EnglishCountryRegionName] NVARCHAR(50),
	[SpanishCountryRegionName] NVARCHAR(50),
	[FrenchCountryRegionName] NVARCHAR(50),
	[PostalCode] NVARCHAR(15),
	[SalesTerritoryKey] INT
)WITH (
	CLUSTERED COLUMNSTORE INDEX,
	DISTRIBUTION = ROUND_ROBIN
);
2.1.2.10 Table DimOrganization
2.1.2.10.1 DimOrganization Properties
PropertyValue
TableDimOrganization
Technical name
Activatedtrue
Id
Schemadbo
Additional propertiesfalse
$ref
Comments
If not exist
Distributionround_robin
Persistenceregular
SQL pool indexingclustered columnstore index
Partition
CTAS query
Range for values
Boundary value
Remarks
2.1.2.10.2 DimOrganization Column
ColumnTypeReqKeyDescriptionComments
OrganizationKeyinttruepk, dk
ParentOrganizationKeyintfalsefk
PercentageOfOwnershipnvarchar(16)false
OrganizationNamenvarchar(50)false
CurrencyKeyintfalsefk
2.1.2.10.2.1 Column OrganizationKey
2.1.2.10.2.1.1 OrganizationKey properties
PropertyValue
Business Name OrganizationKey
Technical name
Activated true
Id
Type numeric
Subtype int
Default
Default constraint name
Comments
Not null true
Masked with function
Identity
Primary key true
Foreign table
Foreign field
Relationship type
Relationship name
Cardinality
Unit
Min value
Excl min
Max value
Excl max
Multiple of
Divisible by
Pattern
Enum
Faker function
Sample 4
Remarks
2.1.2.10.2.2 Column ParentOrganizationKey
2.1.2.10.2.2.1 ParentOrganizationKey properties
PropertyValue
Business Name ParentOrganizationKey
Technical name
Activated true
Id
Type numeric
Subtype int
Default
Default constraint name
Comments
Not null
Masked with function
Identity
Primary key
Unique
Foreign table DimOrganization
Foreign field OrganizationKey
Relationship type Foreign Key
Relationship name fk DimOrganization.ParentOrganizationKey to DimOrganization.OrganizationKey
Cardinality 1..n
Unit
Min value
Excl min
Max value
Excl max
Multiple of
Divisible by
Pattern
Enum
Faker function
Sample 14
Remarks
2.1.2.10.2.3 Column PercentageOfOwnership
2.1.2.10.2.3.1 PercentageOfOwnership properties
PropertyValue
Business Name PercentageOfOwnership
Technical name
Activated true
Id
Type char
Subtype nvarchar
Length 16
Default
Collate
Default constraint name
Comments
Not null
Masked with function
Primary key
Unique
Foreign table
Foreign field
Relationship type
Relationship name
Cardinality
Min length
Pattern
Format
Enum
Faker function
Sample 1
Remarks
2.1.2.10.2.4 Column OrganizationName
2.1.2.10.2.4.1 OrganizationName properties
PropertyValue
Business Name OrganizationName
Technical name
Activated true
Id
Type char
Subtype nvarchar
Length 50
Default
Collate
Default constraint name
Comments
Not null
Masked with function
Primary key
Unique
Foreign table
Foreign field
Relationship type
Relationship name
Cardinality
Min length
Pattern
Format
Enum
Faker function
Sample Northwest Division
Remarks
2.1.2.10.2.5 Column CurrencyKey
2.1.2.10.2.5.1 CurrencyKey properties
PropertyValue
Business Name CurrencyKey
Technical name
Activated true
Id
Type numeric
Subtype int
Default
Default constraint name
Comments
Not null
Masked with function
Identity
Primary key
Unique
Foreign table DimCurrency
Foreign field CurrencyKey
Relationship type Foreign Key
Relationship name fk DimOrganization.CurrencyKey to DimCurrency.CurrencyKey
Cardinality 1..n
Unit
Min value
Excl min
Max value
Excl max
Multiple of
Divisible by
Pattern
Enum
Faker function
Sample 100
Remarks
2.1.2.10.3 DimOrganization Composite keys
PropertyValue
Primary key
[1] Constraint name
Key
Unique key
[1] Constraint name
Key
Alternate key
2.1.2.10.4 DimOrganization JSON Schema
{
    "$schema": "http://json-schema.org/draft-04/schema#",
    "type": "object",
    "title": "DimOrganization",
    "additionalProperties": false,
    "properties": {
        "OrganizationKey": {
            "type": "number"
        },
        "ParentOrganizationKey": {
            "type": "number"
        },
        "PercentageOfOwnership": {
            "type": "string"
        },
        "OrganizationName": {
            "type": "string"
        },
        "CurrencyKey": {
            "type": "number"
        }
    },
    "required": [
        "OrganizationKey"
    ]
}
2.1.2.10.5 DimOrganization JSON data
{
    "OrganizationKey": 4,
    "ParentOrganizationKey": 14,
    "PercentageOfOwnership": "1",
    "OrganizationName": "Northwest Division",
    "CurrencyKey": 100
}
2.1.2.10.6 DimOrganization Target Script
IF NOT EXISTS (SELECT * FROM sys.databases WHERE name = N'[hacksqlpool]')
begin
	CREATE DATABASE [hacksqlpool];
	USE [hacksqlpool];
end;

CREATE SCHEMA [dbo];

CREATE TABLE [dbo].[DimOrganization] (
	[OrganizationKey] INT PRIMARY KEY NONCLUSTERED NOT ENFORCED NOT NULL,
	[ParentOrganizationKey] INT,
	[PercentageOfOwnership] NVARCHAR(16),
	[OrganizationName] NVARCHAR(50),
	[CurrencyKey] INT
)WITH (
	CLUSTERED COLUMNSTORE INDEX,
	DISTRIBUTION = ROUND_ROBIN
);
2.1.2.11 Table DimProduct
2.1.2.11.1 DimProduct Properties
PropertyValue
TableDimProduct
Technical name
Activatedtrue
Id
Schemadbo
Additional propertiesfalse
$ref
Comments
If not exist
Distributionround_robin
Persistenceregular
SQL pool indexingclustered columnstore index
Partition
CTAS query
Range for values
Boundary value
Remarks
2.1.2.11.2 DimProduct Column
ColumnTypeReqKeyDescriptionComments
ProductKeyinttruepk
ProductAlternateKeynvarchar(25)false
ProductSubcategoryKeyintfalsefk
WeightUnitMeasureCodenchar(3)false
SizeUnitMeasureCodenchar(3)false
EnglishProductNamenvarchar(50)true
SpanishProductNamenvarchar(50)false
FrenchProductNamenvarchar(50)false
StandardCostmoneyfalse
FinishedGoodsFlagbittrue
Colornvarchar(15)true
SafetyStockLevelsmallintfalse
ReorderPointsmallintfalse
ListPricemoneyfalse
Sizenvarchar(50)false
SizeRangenvarchar(50)false
Weightfloatfalse
DaysToManufactureintfalse
ProductLinenchar(2)false
DealerPricemoneyfalse
Classnchar(2)false
Stylenchar(2)false
ModelNamenvarchar(50)false
EnglishDescriptionnvarchar(400)false
FrenchDescriptionnvarchar(400)false
ChineseDescriptionnvarchar(400)false
ArabicDescriptionnvarchar(400)false
HebrewDescriptionnvarchar(400)false
ThaiDescriptionnvarchar(400)false
GermanDescriptionnvarchar(400)false
JapaneseDescriptionnvarchar(400)false
TurkishDescriptionnvarchar(400)false
StartDatedatetimefalse
EndDatedatetimefalse
Statusnvarchar(7)false
2.1.2.11.2.1 Column ProductKey
2.1.2.11.2.1.1 ProductKey properties
PropertyValue
Business Name ProductKey
Technical name
Activated true
Id
Type numeric
Subtype int
Default
Default constraint name
Comments
Not null true
Masked with function
Identity
Primary key true
Foreign table
Foreign field
Relationship type
Relationship name
Cardinality
Unit
Min value
Excl min
Max value
Excl max
Multiple of
Divisible by
Pattern
Enum
Faker function
Sample 597
Remarks
2.1.2.11.2.2 Column ProductAlternateKey
2.1.2.11.2.2.1 ProductAlternateKey properties
PropertyValue
Business Name ProductAlternateKey
Technical name
Activated true
Id
Type char
Subtype nvarchar
Length 25
Default
Collate
Default constraint name
Comments
Not null
Masked with function
Primary key
Unique
Foreign table
Foreign field
Relationship type
Relationship name
Cardinality
Min length
Pattern
Format
Enum
Faker function
Sample BK-M18B-42
Remarks
2.1.2.11.2.3 Column ProductSubcategoryKey
2.1.2.11.2.3.1 ProductSubcategoryKey properties
PropertyValue
Business Name ProductSubcategoryKey
Technical name
Activated true
Id
Type numeric
Subtype int
Default
Default constraint name
Comments
Not null
Masked with function
Identity
Primary key
Unique
Foreign table DimProductSubcategory
Foreign field ProductSubcategoryKey
Relationship type Foreign Key
Relationship name fk DimProduct.ProductSubcategoryKey to DimProductSubcategory.ProductSubcategoryKey
Cardinality 1..n
Unit
Min value
Excl min
Max value
Excl max
Multiple of
Divisible by
Pattern
Enum
Faker function
Sample 1
Remarks
2.1.2.11.2.4 Column WeightUnitMeasureCode
2.1.2.11.2.4.1 WeightUnitMeasureCode properties
PropertyValue
Business Name WeightUnitMeasureCode
Technical name
Activated true
Id
Type char
Subtype nchar
Length 3
Default
Collate
Default constraint name
Comments
Not null
Masked with function
Primary key
Unique
Foreign table
Foreign field
Relationship type
Relationship name
Cardinality
Min length
Pattern
Format
Enum
Faker function
Sample LB
Remarks
2.1.2.11.2.5 Column SizeUnitMeasureCode
2.1.2.11.2.5.1 SizeUnitMeasureCode properties
PropertyValue
Business Name SizeUnitMeasureCode
Technical name
Activated true
Id
Type char
Subtype nchar
Length 3
Default
Collate
Default constraint name
Comments
Not null
Masked with function
Primary key
Unique
Foreign table
Foreign field
Relationship type
Relationship name
Cardinality
Min length
Pattern
Format
Enum
Faker function
Sample CM
Remarks
2.1.2.11.2.6 Column EnglishProductName
2.1.2.11.2.6.1 EnglishProductName properties
PropertyValue
Business Name EnglishProductName
Technical name
Activated true
Id
Type char
Subtype nvarchar
Length 50
Default
Collate
Default constraint name
Comments
Not null true
Masked with function
Primary key
Unique
Foreign table
Foreign field
Relationship type
Relationship name
Cardinality
Min length
Pattern
Format
Enum
Faker function
Sample Mountain-500 Black, 42
Remarks
2.1.2.11.2.7 Column SpanishProductName
2.1.2.11.2.7.1 SpanishProductName properties
PropertyValue
Business Name SpanishProductName
Technical name
Activated true
Id
Type char
Subtype nvarchar
Length 50
Default
Collate
Default constraint name
Comments
Not null
Masked with function
Primary key
Unique
Foreign table
Foreign field
Relationship type
Relationship name
Cardinality
Min length
Pattern
Format
Enum
Faker function
Sample Montaña: 500, negra, 42
Remarks
2.1.2.11.2.8 Column FrenchProductName
2.1.2.11.2.8.1 FrenchProductName properties
PropertyValue
Business Name FrenchProductName
Technical name
Activated true
Id
Type char
Subtype nvarchar
Length 50
Default
Collate
Default constraint name
Comments
Not null
Masked with function
Primary key
Unique
Foreign table
Foreign field
Relationship type
Relationship name
Cardinality
Min length
Pattern
Format
Enum
Faker function
Sample VTT 500 noir, 42
Remarks
2.1.2.11.2.9 Column StandardCost
2.1.2.11.2.9.1 StandardCost properties
PropertyValue
Business Name StandardCost
Technical name
Activated true
Id
Type numeric
Subtype money
Default
Default constraint name
Comments
Not null
Masked with function
Identity
Primary key
Unique
Foreign table
Foreign field
Relationship type
Relationship name
Cardinality
Unit
Min value
Excl min
Max value
Excl max
Multiple of
Divisible by
Pattern
Enum
Faker function
Sample 294.5797
Remarks
2.1.2.11.2.10 Column FinishedGoodsFlag
2.1.2.11.2.10.1 FinishedGoodsFlag properties
PropertyValue
Business Name FinishedGoodsFlag
Technical name
Activated true
Id
Type numeric
Subtype bit
Default
Default constraint name
Comments
Not null true
Masked with function
Identity
Primary key
Unique
Foreign table
Foreign field
Relationship type
Relationship name
Cardinality
Unit
Min value
Excl min
Max value
Excl max
Multiple of
Divisible by
Pattern
Enum
Faker function
Sample true
Remarks
2.1.2.11.2.11 Column Color
2.1.2.11.2.11.1 Color properties
PropertyValue
Business Name Color
Technical name
Activated true
Id
Type char
Subtype nvarchar
Length 15
Default
Collate
Default constraint name
Comments
Not null true
Masked with function
Primary key
Unique
Foreign table
Foreign field
Relationship type
Relationship name
Cardinality
Min length
Pattern
Format
Enum
Faker function
Sample Black
Remarks
2.1.2.11.2.12 Column SafetyStockLevel
2.1.2.11.2.12.1 SafetyStockLevel properties
PropertyValue
Business Name SafetyStockLevel
Technical name
Activated true
Id
Type numeric
Subtype smallint
Default
Default constraint name
Comments
Not null
Masked with function
Identity
Primary key
Unique
Foreign table
Foreign field
Relationship type
Relationship name
Cardinality
Unit
Min value
Excl min
Max value
Excl max
Multiple of
Divisible by
Pattern
Enum
Faker function
Sample 100
Remarks
2.1.2.11.2.13 Column ReorderPoint
2.1.2.11.2.13.1 ReorderPoint properties
PropertyValue
Business Name ReorderPoint
Technical name
Activated true
Id
Type numeric
Subtype smallint
Default
Default constraint name
Comments
Not null
Masked with function
Identity
Primary key
Unique
Foreign table
Foreign field
Relationship type
Relationship name
Cardinality
Unit
Min value
Excl min
Max value
Excl max
Multiple of
Divisible by
Pattern
Enum
Faker function
Sample 75
Remarks
2.1.2.11.2.14 Column ListPrice
2.1.2.11.2.14.1 ListPrice properties
PropertyValue
Business Name ListPrice
Technical name
Activated true
Id
Type numeric
Subtype money
Default
Default constraint name
Comments
Not null
Masked with function
Identity
Primary key
Unique
Foreign table
Foreign field
Relationship type
Relationship name
Cardinality
Unit
Min value
Excl min
Max value
Excl max
Multiple of
Divisible by
Pattern
Enum
Faker function
Sample 539.99
Remarks
2.1.2.11.2.15 Column Size
2.1.2.11.2.15.1 Size properties
PropertyValue
Business Name Size
Technical name
Activated true
Id
Type char
Subtype nvarchar
Length 50
Default
Collate
Default constraint name
Comments
Not null
Masked with function
Primary key
Unique
Foreign table
Foreign field
Relationship type
Relationship name
Cardinality
Min length
Pattern
Format
Enum
Faker function
Sample 42
Remarks
2.1.2.11.2.16 Column SizeRange
2.1.2.11.2.16.1 SizeRange properties
PropertyValue
Business Name SizeRange
Technical name
Activated true
Id
Type char
Subtype nvarchar
Length 50
Default
Collate
Default constraint name
Comments
Not null
Masked with function
Primary key
Unique
Foreign table
Foreign field
Relationship type
Relationship name
Cardinality
Min length
Pattern
Format
Enum
Faker function
Sample 42-46 CM
Remarks
2.1.2.11.2.17 Column Weight
2.1.2.11.2.17.1 Weight properties
PropertyValue
Business Name Weight
Technical name
Activated true
Id
Type numeric
Subtype float
Default
Default constraint name
Comments
Not null
Masked with function
Identity
Primary key
Unique
Foreign table
Foreign field
Relationship type
Relationship name
Cardinality
Unit
Min value
Excl min
Max value
Excl max
Multiple of
Divisible by
Pattern
Enum
Faker function
Sample 27.77
Remarks
2.1.2.11.2.18 Column DaysToManufacture
2.1.2.11.2.18.1 DaysToManufacture properties
PropertyValue
Business Name DaysToManufacture
Technical name
Activated true
Id
Type numeric
Subtype int
Default
Default constraint name
Comments
Not null
Masked with function
Identity
Primary key
Unique
Foreign table
Foreign field
Relationship type
Relationship name
Cardinality
Unit
Min value
Excl min
Max value
Excl max
Multiple of
Divisible by
Pattern
Enum
Faker function
Sample 4
Remarks
2.1.2.11.2.19 Column ProductLine
2.1.2.11.2.19.1 ProductLine properties
PropertyValue
Business Name ProductLine
Technical name
Activated true
Id
Type char
Subtype nchar
Length 2
Default
Collate
Default constraint name
Comments
Not null
Masked with function
Primary key
Unique
Foreign table
Foreign field
Relationship type
Relationship name
Cardinality
Min length
Pattern
Format
Enum
Faker function
Sample M
Remarks
2.1.2.11.2.20 Column DealerPrice
2.1.2.11.2.20.1 DealerPrice properties
PropertyValue
Business Name DealerPrice
Technical name
Activated true
Id
Type numeric
Subtype money
Default
Default constraint name
Comments
Not null
Masked with function
Identity
Primary key
Unique
Foreign table
Foreign field
Relationship type
Relationship name
Cardinality
Unit
Min value
Excl min
Max value
Excl max
Multiple of
Divisible by
Pattern
Enum
Faker function
Sample 323.994
Remarks
2.1.2.11.2.21 Column Class
2.1.2.11.2.21.1 Class properties
PropertyValue
Business Name Class
Technical name
Activated true
Id
Type char
Subtype nchar
Length 2
Default
Collate
Default constraint name
Comments
Not null
Masked with function
Primary key
Unique
Foreign table
Foreign field
Relationship type
Relationship name
Cardinality
Min length
Pattern
Format
Enum
Faker function
Sample L
Remarks
2.1.2.11.2.22 Column Style
2.1.2.11.2.22.1 Style properties
PropertyValue
Business Name Style
Technical name
Activated true
Id
Type char
Subtype nchar
Length 2
Default
Collate
Default constraint name
Comments
Not null
Masked with function
Primary key
Unique
Foreign table
Foreign field
Relationship type
Relationship name
Cardinality
Min length
Pattern
Format
Enum
Faker function
Sample U
Remarks
2.1.2.11.2.23 Column ModelName
2.1.2.11.2.23.1 ModelName properties
PropertyValue
Business Name ModelName
Technical name
Activated true
Id
Type char
Subtype nvarchar
Length 50
Default
Collate
Default constraint name
Comments
Not null
Masked with function
Primary key
Unique
Foreign table
Foreign field
Relationship type
Relationship name
Cardinality
Min length
Pattern
Format
Enum
Faker function
Sample Mountain-500
Remarks
2.1.2.11.2.24 Column EnglishDescription
2.1.2.11.2.24.1 EnglishDescription properties
PropertyValue
Business Name EnglishDescription
Technical name
Activated true
Id
Type char
Subtype nvarchar
Length 400
Default
Collate
Default constraint name
Comments
Not null
Masked with function
Primary key
Unique
Foreign table
Foreign field
Relationship type
Relationship name
Cardinality
Min length
Pattern
Format
Enum
Faker function
Sample Suitable for any type of riding, on or off-road. Fits any budget. Smooth-shifting with a comfortable ride.
Remarks
2.1.2.11.2.25 Column FrenchDescription
2.1.2.11.2.25.1 FrenchDescription properties
PropertyValue
Business Name FrenchDescription
Technical name
Activated true
Id
Type char
Subtype nvarchar
Length 400
Default
Collate
Default constraint name
Comments
Not null
Masked with function
Primary key
Unique
Foreign table
Foreign field
Relationship type
Relationship name
Cardinality
Min length
Pattern
Format
Enum
Faker function
Sample Adapté à tous les usages, sur route ou tout-terrain. Pour toutes les bourses. Changement de braquet en douceur et conduite confortable.
Remarks
2.1.2.11.2.26 Column ChineseDescription
2.1.2.11.2.26.1 ChineseDescription properties
PropertyValue
Business Name ChineseDescription
Technical name
Activated true
Id
Type char
Subtype nvarchar
Length 400
Default
Collate
Default constraint name
Comments
Not null
Masked with function
Primary key
Unique
Foreign table
Foreign field
Relationship type
Relationship name
Cardinality
Min length
Pattern
Format
Enum
Faker function
Sample 适合所有类型的使用,不论是公路骑乘还是越野。不论预算多少,均可称心如意。变速平稳,骑乘舒适。
Remarks
2.1.2.11.2.27 Column ArabicDescription
2.1.2.11.2.27.1 ArabicDescription properties
PropertyValue
Business Name ArabicDescription
Technical name
Activated true
Id
Type char
Subtype nvarchar
Length 400
Default
Collate
Default constraint name
Comments
Not null
Masked with function
Primary key
Unique
Foreign table
Foreign field
Relationship type
Relationship name
Cardinality
Min length
Pattern
Format
Enum
Faker function
Sample ملائمة لأي نوع من أنواع القيادة، سواءً على الطرق الممهدة أو غير الممهدة. وتناسب أية ميزانية. نقل سرعات سلس مع قيادة مريحة.
Remarks
2.1.2.11.2.28 Column HebrewDescription
2.1.2.11.2.28.1 HebrewDescription properties
PropertyValue
Business Name HebrewDescription
Technical name
Activated true
Id
Type char
Subtype nvarchar
Length 400
Default
Collate
Default constraint name
Comments
Not null
Masked with function
Primary key
Unique
Foreign table
Foreign field
Relationship type
Relationship name
Cardinality
Min length
Pattern
Format
Enum
Faker function
Sample מתאימים לכל סוג רכיבה, בדרכים סלולות ובשבילים כאחת. מתאימים לכל תקציב. החלפת הילוכים חלקה יחד עם רכיבה נוחה.
Remarks
2.1.2.11.2.29 Column ThaiDescription
2.1.2.11.2.29.1 ThaiDescription properties
PropertyValue
Business Name ThaiDescription
Technical name
Activated true
Id
Type char
Subtype nvarchar
Length 400
Default
Collate
Default constraint name
Comments
Not null
Masked with function
Primary key
Unique
Foreign table
Foreign field
Relationship type
Relationship name
Cardinality
Min length
Pattern
Format
Enum
Faker function
Sample เหมาะสำหรับการขี่ทุกประเภท ทั้งบนถนนและแบบออฟโรด ในราคาย่อมเยา เปลี่ยนเกียร์อย่างนุ่มนวล พร้อมการขับขี่ที่แสนสบาย
Remarks
2.1.2.11.2.30 Column GermanDescription
2.1.2.11.2.30.1 GermanDescription properties
PropertyValue
Business Name GermanDescription
Technical name
Activated true
Id
Type char
Subtype nvarchar
Length 400
Default
Collate
Default constraint name
Comments
Not null
Masked with function
Primary key
Unique
Foreign table
Foreign field
Relationship type
Relationship name
Cardinality
Min length
Pattern
Format
Enum
Faker function
Sample Geeignet für Straße und Gelände. Für jedes Budget. Komfortable Gangschaltung.
Remarks
2.1.2.11.2.31 Column JapaneseDescription
2.1.2.11.2.31.1 JapaneseDescription properties
PropertyValue
Business Name JapaneseDescription
Technical name
Activated true
Id
Type char
Subtype nvarchar
Length 400
Default
Collate
Default constraint name
Comments
Not null
Masked with function
Primary key
Unique
Foreign table
Foreign field
Relationship type
Relationship name
Cardinality
Min length
Pattern
Format
Enum
Faker function
Sample オンロードにもオフロードにも対応しながら、予算を選ばず、スムーズなシフトで快適な乗り心地です。
Remarks
2.1.2.11.2.32 Column TurkishDescription
2.1.2.11.2.32.1 TurkishDescription properties
PropertyValue
Business Name TurkishDescription
Technical name
Activated true
Id
Type char
Subtype nvarchar
Length 400
Default
Collate
Default constraint name
Comments
Not null
Masked with function
Primary key
Unique
Foreign table
Foreign field
Relationship type
Relationship name
Cardinality
Min length
Pattern
Format
Enum
Faker function
Sample Yolda veya arazide her tür sürüşe uygundur. Her bütçeye uyar. Yumuşak vites geçişi ve konforlu sürüş.
Remarks
2.1.2.11.2.33 Column StartDate
2.1.2.11.2.33.1 StartDate properties
PropertyValue
Business Name StartDate
Technical name
Activated true
Id
Type datetime
Subtype datetime
Comments
Not null
Masked with function
Primary key
Unique
Pattern
Default
Default constraint name
Enum
Faker function
Sample 2003-07-01T00:00:00.000Z
Remarks
2.1.2.11.2.34 Column EndDate
2.1.2.11.2.34.1 EndDate properties
PropertyValue
Business Name EndDate
Technical name
Activated true
Id
Type datetime
Subtype datetime
Comments
Not null
Masked with function
Primary key
Unique
Pattern
Default
Default constraint name
Enum
Faker function
Sample 2003-06-30T00:00:00.000Z
Remarks
2.1.2.11.2.35 Column Status
2.1.2.11.2.35.1 Status properties
PropertyValue
Business Name Status
Technical name
Activated true
Id
Type char
Subtype nvarchar
Length 7
Default
Collate
Default constraint name
Comments
Not null
Masked with function
Primary key
Unique
Foreign table
Foreign field
Relationship type
Relationship name
Cardinality
Min length
Pattern
Format
Enum
Faker function
Sample Current
Remarks
2.1.2.11.3 DimProduct Composite keys
PropertyValue
Primary key
[1] Constraint name
Key
Unique key
[1] Constraint name
Key
Alternate key
2.1.2.11.4 DimProduct JSON Schema
{
    "$schema": "http://json-schema.org/draft-04/schema#",
    "type": "object",
    "title": "DimProduct",
    "additionalProperties": false,
    "properties": {
        "ProductKey": {
            "type": "number"
        },
        "ProductAlternateKey": {
            "type": "string"
        },
        "ProductSubcategoryKey": {
            "type": "number"
        },
        "WeightUnitMeasureCode": {
            "type": "string"
        },
        "SizeUnitMeasureCode": {
            "type": "string"
        },
        "EnglishProductName": {
            "type": "string"
        },
        "SpanishProductName": {
            "type": "string"
        },
        "FrenchProductName": {
            "type": "string"
        },
        "StandardCost": {
            "type": "number"
        },
        "FinishedGoodsFlag": {
            "type": "number"
        },
        "Color": {
            "type": "string"
        },
        "SafetyStockLevel": {
            "type": "number"
        },
        "ReorderPoint": {
            "type": "number"
        },
        "ListPrice": {
            "type": "number"
        },
        "Size": {
            "type": "string"
        },
        "SizeRange": {
            "type": "string"
        },
        "Weight": {
            "type": "number"
        },
        "DaysToManufacture": {
            "type": "number"
        },
        "ProductLine": {
            "type": "string"
        },
        "DealerPrice": {
            "type": "number"
        },
        "Class": {
            "type": "string"
        },
        "Style": {
            "type": "string"
        },
        "ModelName": {
            "type": "string"
        },
        "EnglishDescription": {
            "type": "string"
        },
        "FrenchDescription": {
            "type": "string"
        },
        "ChineseDescription": {
            "type": "string"
        },
        "ArabicDescription": {
            "type": "string"
        },
        "HebrewDescription": {
            "type": "string"
        },
        "ThaiDescription": {
            "type": "string"
        },
        "GermanDescription": {
            "type": "string"
        },
        "JapaneseDescription": {
            "type": "string"
        },
        "TurkishDescription": {
            "type": "string"
        },
        "StartDate": {
            "type": "string",
            "maxLength": 24
        },
        "EndDate": {
            "type": "string",
            "maxLength": 24
        },
        "Status": {
            "type": "string"
        }
    },
    "required": [
        "ProductKey",
        "EnglishProductName",
        "FinishedGoodsFlag",
        "Color"
    ]
}
2.1.2.11.5 DimProduct JSON data
{
    "ProductKey": 597,
    "ProductAlternateKey": "BK-M18B-42",
    "ProductSubcategoryKey": 1,
    "WeightUnitMeasureCode": "LB ",
    "SizeUnitMeasureCode": "CM ",
    "EnglishProductName": "Mountain-500 Black, 42",
    "SpanishProductName": "Montaña: 500, negra, 42",
    "FrenchProductName": "VTT 500 noir, 42",
    "StandardCost": 294.5797,
    "FinishedGoodsFlag": true,
    "Color": "Black",
    "SafetyStockLevel": 100,
    "ReorderPoint": 75,
    "ListPrice": 539.99,
    "Size": "42",
    "SizeRange": "42-46 CM",
    "Weight": 27.77,
    "DaysToManufacture": 4,
    "ProductLine": "M ",
    "DealerPrice": 323.994,
    "Class": "L ",
    "Style": "U ",
    "ModelName": "Mountain-500",
    "EnglishDescription": "Suitable for any type of riding, on or off-road. Fits any budget. Smooth-shifting with a comfortable ride.",
    "FrenchDescription": "Adapté à tous les usages, sur route ou tout-terrain. Pour toutes les bourses. Changement de braquet en douceur et conduite confortable.",
    "ChineseDescription": "适合所有类型的使用,不论是公路骑乘还是越野。不论预算多少,均可称心如意。变速平稳,骑乘舒适。",
    "ArabicDescription": "ملائمة لأي نوع من أنواع القيادة، سواءً على الطرق الممهدة أو غير الممهدة. وتناسب أية ميزانية. نقل سرعات سلس مع قيادة مريحة.",
    "HebrewDescription": "מתאימים לכל סוג רכיבה, בדרכים סלולות ובשבילים כאחת. מתאימים לכל תקציב. החלפת הילוכים חלקה יחד עם רכיבה נוחה.",
    "ThaiDescription": "เหมาะสำหรับการขี่ทุกประเภท ทั้งบนถนนและแบบออฟโรด  ในราคาย่อมเยา เปลี่ยนเกียร์อย่างนุ่มนวล พร้อมการขับขี่ที่แสนสบาย",
    "GermanDescription": "Geeignet für Straße und Gelände. Für jedes Budget. Komfortable Gangschaltung.",
    "JapaneseDescription": "オンロードにもオフロードにも対応しながら、予算を選ばず、スムーズなシフトで快適な乗り心地です。",
    "TurkishDescription": "Yolda veya arazide her tür sürüşe uygundur. Her bütçeye uyar. Yumuşak vites geçişi ve konforlu sürüş.",
    "StartDate": "2003-07-01T00:00:00.000Z",
    "EndDate": "2003-06-30T00:00:00.000Z",
    "Status": "Current"
}
2.1.2.11.6 DimProduct Target Script
IF NOT EXISTS (SELECT * FROM sys.databases WHERE name = N'[hacksqlpool]')
begin
	CREATE DATABASE [hacksqlpool];
	USE [hacksqlpool];
end;

CREATE SCHEMA [dbo];

CREATE TABLE [dbo].[DimProduct] (
	[ProductKey] INT PRIMARY KEY NONCLUSTERED NOT ENFORCED NOT NULL,
	[ProductAlternateKey] NVARCHAR(25),
	[ProductSubcategoryKey] INT,
	[WeightUnitMeasureCode] NCHAR(3),
	[SizeUnitMeasureCode] NCHAR(3),
	[EnglishProductName] NVARCHAR(50) NOT NULL,
	[SpanishProductName] NVARCHAR(50),
	[FrenchProductName] NVARCHAR(50),
	[StandardCost] MONEY,
	[FinishedGoodsFlag] BIT NOT NULL,
	[Color] NVARCHAR(15) NOT NULL,
	[SafetyStockLevel] SMALLINT,
	[ReorderPoint] SMALLINT,
	[ListPrice] MONEY,
	[Size] NVARCHAR(50),
	[SizeRange] NVARCHAR(50),
	[Weight] FLOAT,
	[DaysToManufacture] INT,
	[ProductLine] NCHAR(2),
	[DealerPrice] MONEY,
	[Class] NCHAR(2),
	[Style] NCHAR(2),
	[ModelName] NVARCHAR(50),
	[EnglishDescription] NVARCHAR(400),
	[FrenchDescription] NVARCHAR(400),
	[ChineseDescription] NVARCHAR(400),
	[ArabicDescription] NVARCHAR(400),
	[HebrewDescription] NVARCHAR(400),
	[ThaiDescription] NVARCHAR(400),
	[GermanDescription] NVARCHAR(400),
	[JapaneseDescription] NVARCHAR(400),
	[TurkishDescription] NVARCHAR(400),
	[StartDate] DATETIME,
	[EndDate] DATETIME,
	[Status] NVARCHAR(7)
)WITH (
	CLUSTERED COLUMNSTORE INDEX,
	DISTRIBUTION = ROUND_ROBIN
);
2.1.2.12 Table DimProductCategory
2.1.2.12.1 DimProductCategory Properties
PropertyValue
TableDimProductCategory
Technical name
Activatedtrue
Id
Schemadbo
Additional propertiesfalse
$ref
Comments
If not exist
Distributionround_robin
Persistenceregular
SQL pool indexingclustered columnstore index
Partition
CTAS query
Range for values
Boundary value
Remarks
2.1.2.12.2 DimProductCategory Column
ColumnTypeReqKeyDescriptionComments
ProductCategoryKeyinttruepk, dk
ProductCategoryAlternateKeyintfalse
EnglishProductCategoryNamenvarchar(50)true
SpanishProductCategoryNamenvarchar(50)true
FrenchProductCategoryNamenvarchar(50)true
2.1.2.12.2.1 Column ProductCategoryKey
2.1.2.12.2.1.1 ProductCategoryKey properties
PropertyValue
Business Name ProductCategoryKey
Technical name
Activated true
Id
Type numeric
Subtype int
Default
Default constraint name
Comments
Not null true
Masked with function
Identity
Primary key true
Foreign table
Foreign field
Relationship type
Relationship name
Cardinality
Unit
Min value
Excl min
Max value
Excl max
Multiple of
Divisible by
Pattern
Enum
Faker function
Sample 4
Remarks
2.1.2.12.2.2 Column ProductCategoryAlternateKey
2.1.2.12.2.2.1 ProductCategoryAlternateKey properties
PropertyValue
Business Name ProductCategoryAlternateKey
Technical name
Activated true
Id
Type numeric
Subtype int
Default
Default constraint name
Comments
Not null
Masked with function
Identity
Primary key
Unique
Foreign table
Foreign field
Relationship type
Relationship name
Cardinality
Unit
Min value
Excl min
Max value
Excl max
Multiple of
Divisible by
Pattern
Enum
Faker function
Sample 4
Remarks
2.1.2.12.2.3 Column EnglishProductCategoryName
2.1.2.12.2.3.1 EnglishProductCategoryName properties
PropertyValue
Business Name EnglishProductCategoryName
Technical name
Activated true
Id
Type char
Subtype nvarchar
Length 50
Default
Collate
Default constraint name
Comments
Not null true
Masked with function
Primary key
Unique
Foreign table
Foreign field
Relationship type
Relationship name
Cardinality
Min length
Pattern
Format
Enum
Faker function
Sample Accessories
Remarks
2.1.2.12.2.4 Column SpanishProductCategoryName
2.1.2.12.2.4.1 SpanishProductCategoryName properties
PropertyValue
Business Name SpanishProductCategoryName
Technical name
Activated true
Id
Type char
Subtype nvarchar
Length 50
Default
Collate
Default constraint name
Comments
Not null true
Masked with function
Primary key
Unique
Foreign table
Foreign field
Relationship type
Relationship name
Cardinality
Min length
Pattern
Format
Enum
Faker function
Sample Accesorio
Remarks
2.1.2.12.2.5 Column FrenchProductCategoryName
2.1.2.12.2.5.1 FrenchProductCategoryName properties
PropertyValue
Business Name FrenchProductCategoryName
Technical name
Activated true
Id
Type char
Subtype nvarchar
Length 50
Default
Collate
Default constraint name
Comments
Not null true
Masked with function
Primary key
Unique
Foreign table
Foreign field
Relationship type
Relationship name
Cardinality
Min length
Pattern
Format
Enum
Faker function
Sample Accessoire
Remarks
2.1.2.12.3 DimProductCategory Composite keys
PropertyValue
Primary key
[1] Constraint name
Key
Unique key
[1] Constraint name
Key
Alternate key
2.1.2.12.4 DimProductCategory JSON Schema
{
    "$schema": "http://json-schema.org/draft-04/schema#",
    "type": "object",
    "title": "DimProductCategory",
    "additionalProperties": false,
    "properties": {
        "ProductCategoryKey": {
            "type": "number"
        },
        "ProductCategoryAlternateKey": {
            "type": "number"
        },
        "EnglishProductCategoryName": {
            "type": "string"
        },
        "SpanishProductCategoryName": {
            "type": "string"
        },
        "FrenchProductCategoryName": {
            "type": "string"
        }
    },
    "required": [
        "ProductCategoryKey",
        "EnglishProductCategoryName",
        "SpanishProductCategoryName",
        "FrenchProductCategoryName"
    ]
}
2.1.2.12.5 DimProductCategory JSON data
{
    "ProductCategoryKey": 4,
    "ProductCategoryAlternateKey": 4,
    "EnglishProductCategoryName": "Accessories",
    "SpanishProductCategoryName": "Accesorio",
    "FrenchProductCategoryName": "Accessoire"
}
2.1.2.12.6 DimProductCategory Target Script
IF NOT EXISTS (SELECT * FROM sys.databases WHERE name = N'[hacksqlpool]')
begin
	CREATE DATABASE [hacksqlpool];
	USE [hacksqlpool];
end;

CREATE SCHEMA [dbo];

CREATE TABLE [dbo].[DimProductCategory] (
	[ProductCategoryKey] INT PRIMARY KEY NONCLUSTERED NOT ENFORCED NOT NULL,
	[ProductCategoryAlternateKey] INT,
	[EnglishProductCategoryName] NVARCHAR(50) NOT NULL,
	[SpanishProductCategoryName] NVARCHAR(50) NOT NULL,
	[FrenchProductCategoryName] NVARCHAR(50) NOT NULL
)WITH (
	CLUSTERED COLUMNSTORE INDEX,
	DISTRIBUTION = ROUND_ROBIN
);
2.1.2.13 Table DimProductSubcategory
2.1.2.13.1 DimProductSubcategory Properties
PropertyValue
TableDimProductSubcategory
Technical name
Activatedtrue
Id
Schemadbo
Additional propertiesfalse
$ref
Comments
If not exist
Distributionround_robin
Persistenceregular
SQL pool indexingclustered columnstore index
Partition
CTAS query
Range for values
Boundary value
Remarks
2.1.2.13.2 DimProductSubcategory Column
ColumnTypeReqKeyDescriptionComments
ProductSubcategoryKeyinttruepk, dk
ProductSubcategoryAlternateKeyintfalse
EnglishProductSubcategoryNamenvarchar(50)true
SpanishProductSubcategoryNamenvarchar(50)true
FrenchProductSubcategoryNamenvarchar(50)true
ProductCategoryKeyintfalsefk
2.1.2.13.2.1 Column ProductSubcategoryKey
2.1.2.13.2.1.1 ProductSubcategoryKey properties
PropertyValue
Business Name ProductSubcategoryKey
Technical name
Activated true
Id
Type numeric
Subtype int
Default
Default constraint name
Comments
Not null true
Masked with function
Identity
Primary key true
Foreign table
Foreign field
Relationship type
Relationship name
Cardinality
Unit
Min value
Excl min
Max value
Excl max
Multiple of
Divisible by
Pattern
Enum
Faker function
Sample 33
Remarks
2.1.2.13.2.2 Column ProductSubcategoryAlternateKey
2.1.2.13.2.2.1 ProductSubcategoryAlternateKey properties
PropertyValue
Business Name ProductSubcategoryAlternateKey
Technical name
Activated true
Id
Type numeric
Subtype int
Default
Default constraint name
Comments
Not null
Masked with function
Identity
Primary key
Unique
Foreign table
Foreign field
Relationship type
Relationship name
Cardinality
Unit
Min value
Excl min
Max value
Excl max
Multiple of
Divisible by
Pattern
Enum
Faker function
Sample 33
Remarks
2.1.2.13.2.3 Column EnglishProductSubcategoryName
2.1.2.13.2.3.1 EnglishProductSubcategoryName properties
PropertyValue
Business Name EnglishProductSubcategoryName
Technical name
Activated true
Id
Type char
Subtype nvarchar
Length 50
Default
Collate
Default constraint name
Comments
Not null true
Masked with function
Primary key
Unique
Foreign table
Foreign field
Relationship type
Relationship name
Cardinality
Min length
Pattern
Format
Enum
Faker function
Sample Lights
Remarks
2.1.2.13.2.4 Column SpanishProductSubcategoryName
2.1.2.13.2.4.1 SpanishProductSubcategoryName properties
PropertyValue
Business Name SpanishProductSubcategoryName
Technical name
Activated true
Id
Type char
Subtype nvarchar
Length 50
Default
Collate
Default constraint name
Comments
Not null true
Masked with function
Primary key
Unique
Foreign table
Foreign field
Relationship type
Relationship name
Cardinality
Min length
Pattern
Format
Enum
Faker function
Sample Luz
Remarks
2.1.2.13.2.5 Column FrenchProductSubcategoryName
2.1.2.13.2.5.1 FrenchProductSubcategoryName properties
PropertyValue
Business Name FrenchProductSubcategoryName
Technical name
Activated true
Id
Type char
Subtype nvarchar
Length 50
Default
Collate
Default constraint name
Comments
Not null true
Masked with function
Primary key
Unique
Foreign table
Foreign field
Relationship type
Relationship name
Cardinality
Min length
Pattern
Format
Enum
Faker function
Sample Éclairage
Remarks
2.1.2.13.2.6 Column ProductCategoryKey
2.1.2.13.2.6.1 ProductCategoryKey properties
PropertyValue
Business Name ProductCategoryKey
Technical name
Activated true
Id
Type numeric
Subtype int
Default
Default constraint name
Comments
Not null
Masked with function
Identity
Primary key
Unique
Foreign table DimProductCategory
Foreign field ProductCategoryKey
Relationship type Foreign Key
Relationship name fk DimProductSubcategory.ProductCategoryKey to DimProductCategory.ProductCategoryKey
Cardinality 1..n
Unit
Min value
Excl min
Max value
Excl max
Multiple of
Divisible by
Pattern
Enum
Faker function
Sample 4
Remarks
2.1.2.13.3 DimProductSubcategory Composite keys
PropertyValue
Primary key
[1] Constraint name
Key
Unique key
[1] Constraint name
Key
Alternate key
2.1.2.13.4 DimProductSubcategory JSON Schema
{
    "$schema": "http://json-schema.org/draft-04/schema#",
    "type": "object",
    "title": "DimProductSubcategory",
    "additionalProperties": false,
    "properties": {
        "ProductSubcategoryKey": {
            "type": "number"
        },
        "ProductSubcategoryAlternateKey": {
            "type": "number"
        },
        "EnglishProductSubcategoryName": {
            "type": "string"
        },
        "SpanishProductSubcategoryName": {
            "type": "string"
        },
        "FrenchProductSubcategoryName": {
            "type": "string"
        },
        "ProductCategoryKey": {
            "type": "number"
        }
    },
    "required": [
        "ProductSubcategoryKey",
        "EnglishProductSubcategoryName",
        "SpanishProductSubcategoryName",
        "FrenchProductSubcategoryName"
    ]
}
2.1.2.13.5 DimProductSubcategory JSON data
{
    "ProductSubcategoryKey": 33,
    "ProductSubcategoryAlternateKey": 33,
    "EnglishProductSubcategoryName": "Lights",
    "SpanishProductSubcategoryName": "Luz",
    "FrenchProductSubcategoryName": "Éclairage",
    "ProductCategoryKey": 4
}
2.1.2.13.6 DimProductSubcategory Target Script
IF NOT EXISTS (SELECT * FROM sys.databases WHERE name = N'[hacksqlpool]')
begin
	CREATE DATABASE [hacksqlpool];
	USE [hacksqlpool];
end;

CREATE SCHEMA [dbo];

CREATE TABLE [dbo].[DimProductSubcategory] (
	[ProductSubcategoryKey] INT PRIMARY KEY NONCLUSTERED NOT ENFORCED NOT NULL,
	[ProductSubcategoryAlternateKey] INT,
	[EnglishProductSubcategoryName] NVARCHAR(50) NOT NULL,
	[SpanishProductSubcategoryName] NVARCHAR(50) NOT NULL,
	[FrenchProductSubcategoryName] NVARCHAR(50) NOT NULL,
	[ProductCategoryKey] INT
)WITH (
	CLUSTERED COLUMNSTORE INDEX,
	DISTRIBUTION = ROUND_ROBIN
);
2.1.2.14 Table DimPromotion
2.1.2.14.1 DimPromotion Properties
PropertyValue
TableDimPromotion
Technical name
Activatedtrue
Id
Schemadbo
Additional propertiesfalse
$ref
Comments
If not exist
Distributionround_robin
Persistenceregular
SQL pool indexingclustered columnstore index
Partition
CTAS query
Range for values
Boundary value
Remarks
2.1.2.14.2 DimPromotion Column
ColumnTypeReqKeyDescriptionComments
PromotionKeyinttruepk, dk
PromotionAlternateKeyintfalse
EnglishPromotionNamenvarchar(255)false
SpanishPromotionNamenvarchar(255)false
FrenchPromotionNamenvarchar(255)false
DiscountPctfloatfalse
EnglishPromotionTypenvarchar(50)false
SpanishPromotionTypenvarchar(50)false
FrenchPromotionTypenvarchar(50)false
EnglishPromotionCategorynvarchar(50)false
SpanishPromotionCategorynvarchar(50)false
FrenchPromotionCategorynvarchar(50)false
StartDatedatetimetrue
EndDatedatetimefalse
MinQtyintfalse
MaxQtyintfalse
2.1.2.14.2.1 Column PromotionKey
2.1.2.14.2.1.1 PromotionKey properties
PropertyValue
Business Name PromotionKey
Technical name
Activated true
Id
Type numeric
Subtype int
Default
Default constraint name
Comments
Not null true
Masked with function
Identity
Primary key true
Foreign table
Foreign field
Relationship type
Relationship name
Cardinality
Unit
Min value
Excl min
Max value
Excl max
Multiple of
Divisible by
Pattern
Enum
Faker function
Sample 16
Remarks
2.1.2.14.2.2 Column PromotionAlternateKey
2.1.2.14.2.2.1 PromotionAlternateKey properties
PropertyValue
Business Name PromotionAlternateKey
Technical name
Activated true
Id
Type numeric
Subtype int
Default
Default constraint name
Comments
Not null
Masked with function
Identity
Primary key
Unique
Foreign table
Foreign field
Relationship type
Relationship name
Cardinality
Unit
Min value
Excl min
Max value
Excl max
Multiple of
Divisible by
Pattern
Enum
Faker function
Sample 16
Remarks
2.1.2.14.2.3 Column EnglishPromotionName
2.1.2.14.2.3.1 EnglishPromotionName properties
PropertyValue
Business Name EnglishPromotionName
Technical name
Activated true
Id
Type char
Subtype nvarchar
Length 255
Default
Collate
Default constraint name
Comments
Not null
Masked with function
Primary key
Unique
Foreign table
Foreign field
Relationship type
Relationship name
Cardinality
Min length
Pattern
Format
Enum
Faker function
Sample Mountain-500 Silver Clearance Sale
Remarks
2.1.2.14.2.4 Column SpanishPromotionName
2.1.2.14.2.4.1 SpanishPromotionName properties
PropertyValue
Business Name SpanishPromotionName
Technical name
Activated true
Id
Type char
Subtype nvarchar
Length 255
Default
Collate
Default constraint name
Comments
Not null
Masked with function
Primary key
Unique
Foreign table
Foreign field
Relationship type
Relationship name
Cardinality
Min length
Pattern
Format
Enum
Faker function
Sample Liquidación de bicicleta de montaña, 500, plateada
Remarks
2.1.2.14.2.5 Column FrenchPromotionName
2.1.2.14.2.5.1 FrenchPromotionName properties
PropertyValue
Business Name FrenchPromotionName
Technical name
Activated true
Id
Type char
Subtype nvarchar
Length 255
Default
Collate
Default constraint name
Comments
Not null
Masked with function
Primary key
Unique
Foreign table
Foreign field
Relationship type
Relationship name
Cardinality
Min length
Pattern
Format
Enum
Faker function
Sample Liquidation VTT 500 argent
Remarks
2.1.2.14.2.6 Column DiscountPct
2.1.2.14.2.6.1 DiscountPct properties
PropertyValue
Business Name DiscountPct
Technical name
Activated true
Id
Type numeric
Subtype float
Default
Default constraint name
Comments
Not null
Masked with function
Identity
Primary key
Unique
Foreign table
Foreign field
Relationship type
Relationship name
Cardinality
Unit
Min value
Excl min
Max value
Excl max
Multiple of
Divisible by
Pattern
Enum
Faker function
Sample 0.4
Remarks
2.1.2.14.2.7 Column EnglishPromotionType
2.1.2.14.2.7.1 EnglishPromotionType properties
PropertyValue
Business Name EnglishPromotionType
Technical name
Activated true
Id
Type char
Subtype nvarchar
Length 50
Default
Collate
Default constraint name
Comments
Not null
Masked with function
Primary key
Unique
Foreign table
Foreign field
Relationship type
Relationship name
Cardinality
Min length
Pattern
Format
Enum
Faker function
Sample Discontinued Product
Remarks
2.1.2.14.2.8 Column SpanishPromotionType
2.1.2.14.2.8.1 SpanishPromotionType properties
PropertyValue
Business Name SpanishPromotionType
Technical name
Activated true
Id
Type char
Subtype nvarchar
Length 50
Default
Collate
Default constraint name
Comments
Not null
Masked with function
Primary key
Unique
Foreign table
Foreign field
Relationship type
Relationship name
Cardinality
Min length
Pattern
Format
Enum
Faker function
Sample Descatalogado
Remarks
2.1.2.14.2.9 Column FrenchPromotionType
2.1.2.14.2.9.1 FrenchPromotionType properties
PropertyValue
Business Name FrenchPromotionType
Technical name
Activated true
Id
Type char
Subtype nvarchar
Length 50
Default
Collate
Default constraint name
Comments
Not null
Masked with function
Primary key
Unique
Foreign table
Foreign field
Relationship type
Relationship name
Cardinality
Min length
Pattern
Format
Enum
Faker function
Sample Ce produit n'est plus commercialisé
Remarks
2.1.2.14.2.10 Column EnglishPromotionCategory
2.1.2.14.2.10.1 EnglishPromotionCategory properties
PropertyValue
Business Name EnglishPromotionCategory
Technical name
Activated true
Id
Type char
Subtype nvarchar
Length 50
Default
Collate
Default constraint name
Comments
Not null
Masked with function
Primary key
Unique
Foreign table
Foreign field
Relationship type
Relationship name
Cardinality
Min length
Pattern
Format
Enum
Faker function
Sample Reseller
Remarks
2.1.2.14.2.11 Column SpanishPromotionCategory
2.1.2.14.2.11.1 SpanishPromotionCategory properties
PropertyValue
Business Name SpanishPromotionCategory
Technical name
Activated true
Id
Type char
Subtype nvarchar
Length 50
Default
Collate
Default constraint name
Comments
Not null
Masked with function
Primary key
Unique
Foreign table
Foreign field
Relationship type
Relationship name
Cardinality
Min length
Pattern
Format
Enum
Faker function
Sample Distribuidor
Remarks
2.1.2.14.2.12 Column FrenchPromotionCategory
2.1.2.14.2.12.1 FrenchPromotionCategory properties
PropertyValue
Business Name FrenchPromotionCategory
Technical name
Activated true
Id
Type char
Subtype nvarchar
Length 50
Default
Collate
Default constraint name
Comments
Not null
Masked with function
Primary key
Unique
Foreign table
Foreign field
Relationship type
Relationship name
Cardinality
Min length
Pattern
Format
Enum
Faker function
Sample Revendeur
Remarks
2.1.2.14.2.13 Column StartDate
2.1.2.14.2.13.1 StartDate properties
PropertyValue
Business Name StartDate
Technical name
Activated true
Id
Type datetime
Subtype datetime
Comments
Not null true
Masked with function
Primary key
Unique
Pattern
Default
Default constraint name
Enum
Faker function
Sample 2004-05-01T00:00:00.000Z
Remarks
2.1.2.14.2.14 Column EndDate
2.1.2.14.2.14.1 EndDate properties
PropertyValue
Business Name EndDate
Technical name
Activated true
Id
Type datetime
Subtype datetime
Comments
Not null
Masked with function
Primary key
Unique
Pattern
Default
Default constraint name
Enum
Faker function
Sample 2004-06-30T00:00:00.000Z
Remarks
2.1.2.14.2.15 Column MinQty
2.1.2.14.2.15.1 MinQty properties
PropertyValue
Business Name MinQty
Technical name
Activated true
Id
Type numeric
Subtype int
Default
Default constraint name
Comments
Not null
Masked with function
Identity
Primary key
Unique
Foreign table
Foreign field
Relationship type
Relationship name
Cardinality
Unit
Min value
Excl min
Max value
Excl max
Multiple of
Divisible by
Pattern
Enum
Faker function
Sample 0
Remarks
2.1.2.14.2.16 Column MaxQty
2.1.2.14.2.16.1 MaxQty properties
PropertyValue
Business Name MaxQty
Technical name
Activated true
Id
Type numeric
Subtype int
Default
Default constraint name
Comments
Not null
Masked with function
Identity
Primary key
Unique
Foreign table
Foreign field
Relationship type
Relationship name
Cardinality
Unit
Min value
Excl min
Max value
Excl max
Multiple of
Divisible by
Pattern
Enum
Faker function
Sample 40
Remarks
2.1.2.14.3 DimPromotion Composite keys
PropertyValue
Primary key
[1] Constraint name
Key
Unique key
[1] Constraint name
Key
Alternate key
2.1.2.14.4 DimPromotion JSON Schema
{
    "$schema": "http://json-schema.org/draft-04/schema#",
    "type": "object",
    "title": "DimPromotion",
    "additionalProperties": false,
    "properties": {
        "PromotionKey": {
            "type": "number"
        },
        "PromotionAlternateKey": {
            "type": "number"
        },
        "EnglishPromotionName": {
            "type": "string"
        },
        "SpanishPromotionName": {
            "type": "string"
        },
        "FrenchPromotionName": {
            "type": "string"
        },
        "DiscountPct": {
            "type": "number"
        },
        "EnglishPromotionType": {
            "type": "string"
        },
        "SpanishPromotionType": {
            "type": "string"
        },
        "FrenchPromotionType": {
            "type": "string"
        },
        "EnglishPromotionCategory": {
            "type": "string"
        },
        "SpanishPromotionCategory": {
            "type": "string"
        },
        "FrenchPromotionCategory": {
            "type": "string"
        },
        "StartDate": {
            "type": "string",
            "maxLength": 24
        },
        "EndDate": {
            "type": "string",
            "maxLength": 24
        },
        "MinQty": {
            "type": "number"
        },
        "MaxQty": {
            "type": "number"
        }
    },
    "required": [
        "PromotionKey",
        "StartDate"
    ]
}
2.1.2.14.5 DimPromotion JSON data
{
    "PromotionKey": 16,
    "PromotionAlternateKey": 16,
    "EnglishPromotionName": "Mountain-500 Silver Clearance Sale",
    "SpanishPromotionName": "Liquidación de bicicleta de montaña, 500, plateada",
    "FrenchPromotionName": "Liquidation VTT 500 argent",
    "DiscountPct": 0.4,
    "EnglishPromotionType": "Discontinued Product",
    "SpanishPromotionType": "Descatalogado",
    "FrenchPromotionType": "Ce produit n'est plus commercialisé",
    "EnglishPromotionCategory": "Reseller",
    "SpanishPromotionCategory": "Distribuidor",
    "FrenchPromotionCategory": "Revendeur",
    "StartDate": "2004-05-01T00:00:00.000Z",
    "EndDate": "2004-06-30T00:00:00.000Z",
    "MinQty": 0,
    "MaxQty": 40
}
2.1.2.14.6 DimPromotion Target Script
IF NOT EXISTS (SELECT * FROM sys.databases WHERE name = N'[hacksqlpool]')
begin
	CREATE DATABASE [hacksqlpool];
	USE [hacksqlpool];
end;

CREATE SCHEMA [dbo];

CREATE TABLE [dbo].[DimPromotion] (
	[PromotionKey] INT PRIMARY KEY NONCLUSTERED NOT ENFORCED NOT NULL,
	[PromotionAlternateKey] INT,
	[EnglishPromotionName] NVARCHAR(255),
	[SpanishPromotionName] NVARCHAR(255),
	[FrenchPromotionName] NVARCHAR(255),
	[DiscountPct] FLOAT,
	[EnglishPromotionType] NVARCHAR(50),
	[SpanishPromotionType] NVARCHAR(50),
	[FrenchPromotionType] NVARCHAR(50),
	[EnglishPromotionCategory] NVARCHAR(50),
	[SpanishPromotionCategory] NVARCHAR(50),
	[FrenchPromotionCategory] NVARCHAR(50),
	[StartDate] DATETIME NOT NULL,
	[EndDate] DATETIME,
	[MinQty] INT,
	[MaxQty] INT
)WITH (
	CLUSTERED COLUMNSTORE INDEX,
	DISTRIBUTION = ROUND_ROBIN
);
2.1.2.15 Table DimReseller
2.1.2.15.1 DimReseller Properties
PropertyValue
TableDimReseller
Technical name
Activatedtrue
Id
Schemadbo
Additional propertiesfalse
$ref
Comments
If not exist
Distributionround_robin
Persistenceregular
SQL pool indexingclustered columnstore index
Partition
CTAS query
Range for values
Boundary value
Remarks
2.1.2.15.2 DimReseller Column
ColumnTypeReqKeyDescriptionComments
ResellerKeyinttruepk, dk
GeographyKeyintfalsefk
ResellerAlternateKeynvarchar(15)false
Phonenvarchar(25)false
BusinessTypevarchar(20)true
ResellerNamenvarchar(50)true
NumberEmployeesintfalse
OrderFrequencychar(1)false
OrderMonthtinyintfalse
FirstOrderYearintfalse
LastOrderYearintfalse
ProductLinenvarchar(50)false
AddressLine1nvarchar(60)false
AddressLine2nvarchar(60)false
AnnualSalesmoneyfalse
BankNamenvarchar(50)false
MinPaymentTypetinyintfalse
MinPaymentAmountmoneyfalse
AnnualRevenuemoneyfalse
YearOpenedintfalse
2.1.2.15.2.1 Column ResellerKey
2.1.2.15.2.1.1 ResellerKey properties
PropertyValue
Business Name ResellerKey
Technical name
Activated true
Id
Type numeric
Subtype int
Default
Default constraint name
Comments
Not null true
Masked with function
Identity
Primary key true
Foreign table
Foreign field
Relationship type
Relationship name
Cardinality
Unit
Min value
Excl min
Max value
Excl max
Multiple of
Divisible by
Pattern
Enum
Faker function
Sample 687
Remarks
2.1.2.15.2.2 Column GeographyKey
2.1.2.15.2.2.1 GeographyKey properties
PropertyValue
Business Name GeographyKey
Technical name
Activated true
Id
Type numeric
Subtype int
Default
Default constraint name
Comments
Not null
Masked with function
Identity
Primary key
Unique
Foreign table DimGeography
Foreign field GeographyKey
Relationship type Foreign Key
Relationship name fk DimReseller.GeographyKey to DimGeography.GeographyKey
Cardinality 1..n
Unit
Min value
Excl min
Max value
Excl max
Multiple of
Divisible by
Pattern
Enum
Faker function
Sample 159
Remarks
2.1.2.15.2.3 Column ResellerAlternateKey
2.1.2.15.2.3.1 ResellerAlternateKey properties
PropertyValue
Business Name ResellerAlternateKey
Technical name
Activated true
Id
Type char
Subtype nvarchar
Length 15
Default
Collate
Default constraint name
Comments
Not null
Masked with function
Primary key
Unique
Foreign table
Foreign field
Relationship type
Relationship name
Cardinality
Min length
Pattern
Format
Enum
Faker function
Sample AW00000687
Remarks
2.1.2.15.2.4 Column Phone
2.1.2.15.2.4.1 Phone properties
PropertyValue
Business Name Phone
Technical name
Activated true
Id
Type char
Subtype nvarchar
Length 25
Default
Collate
Default constraint name
Comments
Not null
Masked with function
Primary key
Unique
Foreign table
Foreign field
Relationship type
Relationship name
Cardinality
Min length
Pattern
Format
Enum
Faker function
Sample 1 (11) 500 555-0178
Remarks
2.1.2.15.2.5 Column BusinessType
2.1.2.15.2.5.1 BusinessType properties
PropertyValue
Business Name BusinessType
Technical name
Activated true
Id
Type char
Subtype varchar
Length 20
Default
Collate
Default constraint name
Comments
Not null true
Masked with function
Primary key
Unique
Foreign table
Foreign field
Relationship type
Relationship name
Cardinality
Min length
Pattern
Format
Enum
Faker function
Sample Warehouse
Remarks
2.1.2.15.2.6 Column ResellerName
2.1.2.15.2.6.1 ResellerName properties
PropertyValue
Business Name ResellerName
Technical name
Activated true
Id
Type char
Subtype nvarchar
Length 50
Default
Collate
Default constraint name
Comments
Not null true
Masked with function
Primary key
Unique
Foreign table
Foreign field
Relationship type
Relationship name
Cardinality
Min length
Pattern
Format
Enum
Faker function
Sample Functional Store South
Remarks
2.1.2.15.2.7 Column NumberEmployees
2.1.2.15.2.7.1 NumberEmployees properties
PropertyValue
Business Name NumberEmployees
Technical name
Activated true
Id
Type numeric
Subtype int
Default
Default constraint name
Comments
Not null
Masked with function
Identity
Primary key
Unique
Foreign table
Foreign field
Relationship type
Relationship name
Cardinality
Unit
Min value
Excl min
Max value
Excl max
Multiple of
Divisible by
Pattern
Enum
Faker function
Sample 70
Remarks
2.1.2.15.2.8 Column OrderFrequency
2.1.2.15.2.8.1 OrderFrequency properties
PropertyValue
Business Name OrderFrequency
Technical name
Activated true
Id
Type char
Subtype char
Length 1
Default
Collate
Default constraint name
Comments
Not null
Masked with function
Primary key
Unique
Foreign table
Foreign field
Relationship type
Relationship name
Cardinality
Min length
Pattern
Format
Enum
Faker function
Sample Q
Remarks
2.1.2.15.2.9 Column OrderMonth
2.1.2.15.2.9.1 OrderMonth properties
PropertyValue
Business Name OrderMonth
Technical name
Activated true
Id
Type numeric
Subtype tinyint
Default
Default constraint name
Comments
Not null
Masked with function
Identity
Primary key
Unique
Foreign table
Foreign field
Relationship type
Relationship name
Cardinality
Unit
Min value
Excl min
Max value
Excl max
Multiple of
Divisible by
Pattern
Enum
Faker function
Sample 5
Remarks
2.1.2.15.2.10 Column FirstOrderYear
2.1.2.15.2.10.1 FirstOrderYear properties
PropertyValue
Business Name FirstOrderYear
Technical name
Activated true
Id
Type numeric
Subtype int
Default
Default constraint name
Comments
Not null
Masked with function
Identity
Primary key
Unique
Foreign table
Foreign field
Relationship type
Relationship name
Cardinality
Unit
Min value
Excl min
Max value
Excl max
Multiple of
Divisible by
Pattern
Enum
Faker function
Sample 2003
Remarks
2.1.2.15.2.11 Column LastOrderYear
2.1.2.15.2.11.1 LastOrderYear properties
PropertyValue
Business Name LastOrderYear
Technical name
Activated true
Id
Type numeric
Subtype int
Default
Default constraint name
Comments
Not null
Masked with function
Identity
Primary key
Unique
Foreign table
Foreign field
Relationship type
Relationship name
Cardinality
Unit
Min value
Excl min
Max value
Excl max
Multiple of
Divisible by
Pattern
Enum
Faker function
Sample 2004
Remarks
2.1.2.15.2.12 Column ProductLine
2.1.2.15.2.12.1 ProductLine properties
PropertyValue
Business Name ProductLine
Technical name
Activated true
Id
Type char
Subtype nvarchar
Length 50
Default
Collate
Default constraint name
Comments
Not null
Masked with function
Primary key
Unique
Foreign table
Foreign field
Relationship type
Relationship name
Cardinality
Min length
Pattern
Format
Enum
Faker function
Sample Mountain
Remarks
2.1.2.15.2.13 Column AddressLine1
2.1.2.15.2.13.1 AddressLine1 properties
PropertyValue
Business Name AddressLine1
Technical name
Activated true
Id
Type char
Subtype nvarchar
Length 60
Default
Collate
Default constraint name
Comments
Not null
Masked with function
Primary key
Unique
Foreign table
Foreign field
Relationship type
Relationship name
Cardinality
Min length
Pattern
Format
Enum
Faker function
Sample Viktoria-Luise-Platz 475
Remarks
2.1.2.15.2.14 Column AddressLine2
2.1.2.15.2.14.1 AddressLine2 properties
PropertyValue
Business Name AddressLine2
Technical name
Activated true
Id
Type char
Subtype nvarchar
Length 60
Default
Collate
Default constraint name
Comments
Not null
Masked with function
Primary key
Unique
Foreign table
Foreign field
Relationship type
Relationship name
Cardinality
Min length
Pattern
Format
Enum
Faker function
Sample 56 Pitt Street
Remarks
2.1.2.15.2.15 Column AnnualSales
2.1.2.15.2.15.1 AnnualSales properties
PropertyValue
Business Name AnnualSales
Technical name
Activated true
Id
Type numeric
Subtype money
Default
Default constraint name
Comments
Not null
Masked with function
Identity
Primary key
Unique
Foreign table
Foreign field
Relationship type
Relationship name
Cardinality
Unit
Min value
Excl min
Max value
Excl max
Multiple of
Divisible by
Pattern
Enum
Faker function
Sample 3000000
Remarks
2.1.2.15.2.16 Column BankName
2.1.2.15.2.16.1 BankName properties
PropertyValue
Business Name BankName
Technical name
Activated true
Id
Type char
Subtype nvarchar
Length 50
Default
Collate
Default constraint name
Comments
Not null
Masked with function
Primary key
Unique
Foreign table
Foreign field
Relationship type
Relationship name
Cardinality
Min length
Pattern
Format
Enum
Faker function
Sample International Bank
Remarks
2.1.2.15.2.17 Column MinPaymentType
2.1.2.15.2.17.1 MinPaymentType properties
PropertyValue
Business Name MinPaymentType
Technical name
Activated true
Id
Type numeric
Subtype tinyint
Default
Default constraint name
Comments
Not null
Masked with function
Identity
Primary key
Unique
Foreign table
Foreign field
Relationship type
Relationship name
Cardinality
Unit
Min value
Excl min
Max value
Excl max
Multiple of
Divisible by
Pattern
Enum
Faker function
Sample 1
Remarks
2.1.2.15.2.18 Column MinPaymentAmount
2.1.2.15.2.18.1 MinPaymentAmount properties
PropertyValue
Business Name MinPaymentAmount
Technical name
Activated true
Id
Type numeric
Subtype money
Default
Default constraint name
Comments
Not null
Masked with function
Identity
Primary key
Unique
Foreign table
Foreign field
Relationship type
Relationship name
Cardinality
Unit
Min value
Excl min
Max value
Excl max
Multiple of
Divisible by
Pattern
Enum
Faker function
Sample 600
Remarks
2.1.2.15.2.19 Column AnnualRevenue
2.1.2.15.2.19.1 AnnualRevenue properties
PropertyValue
Business Name AnnualRevenue
Technical name
Activated true
Id
Type numeric
Subtype money
Default
Default constraint name
Comments
Not null
Masked with function
Identity
Primary key
Unique
Foreign table
Foreign field
Relationship type
Relationship name
Cardinality
Unit
Min value
Excl min
Max value
Excl max
Multiple of
Divisible by
Pattern
Enum
Faker function
Sample 300000
Remarks
2.1.2.15.2.20 Column YearOpened
2.1.2.15.2.20.1 YearOpened properties
PropertyValue
Business Name YearOpened
Technical name
Activated true
Id
Type numeric
Subtype int
Default
Default constraint name
Comments
Not null
Masked with function
Identity
Primary key
Unique
Foreign table
Foreign field
Relationship type
Relationship name
Cardinality
Unit
Min value
Excl min
Max value
Excl max
Multiple of
Divisible by
Pattern
Enum
Faker function
Sample 1993
Remarks
2.1.2.15.3 DimReseller Composite keys
PropertyValue
Primary key
[1] Constraint name
Key
Unique key
[1] Constraint name
Key
Alternate key
2.1.2.15.4 DimReseller JSON Schema
{
    "$schema": "http://json-schema.org/draft-04/schema#",
    "type": "object",
    "title": "DimReseller",
    "additionalProperties": false,
    "properties": {
        "ResellerKey": {
            "type": "number"
        },
        "GeographyKey": {
            "type": "number"
        },
        "ResellerAlternateKey": {
            "type": "string"
        },
        "Phone": {
            "type": "string"
        },
        "BusinessType": {
            "type": "string"
        },
        "ResellerName": {
            "type": "string"
        },
        "NumberEmployees": {
            "type": "number"
        },
        "OrderFrequency": {
            "type": "string"
        },
        "OrderMonth": {
            "type": "number"
        },
        "FirstOrderYear": {
            "type": "number"
        },
        "LastOrderYear": {
            "type": "number"
        },
        "ProductLine": {
            "type": "string"
        },
        "AddressLine1": {
            "type": "string"
        },
        "AddressLine2": {
            "type": "string"
        },
        "AnnualSales": {
            "type": "number"
        },
        "BankName": {
            "type": "string"
        },
        "MinPaymentType": {
            "type": "number"
        },
        "MinPaymentAmount": {
            "type": "number"
        },
        "AnnualRevenue": {
            "type": "number"
        },
        "YearOpened": {
            "type": "number"
        }
    },
    "required": [
        "ResellerKey",
        "BusinessType",
        "ResellerName"
    ]
}
2.1.2.15.5 DimReseller JSON data
{
    "ResellerKey": 687,
    "GeographyKey": 159,
    "ResellerAlternateKey": "AW00000687",
    "Phone": "1 (11) 500 555-0178",
    "BusinessType": "Warehouse",
    "ResellerName": "Functional Store South",
    "NumberEmployees": 70,
    "OrderFrequency": "Q",
    "OrderMonth": 5,
    "FirstOrderYear": 2003,
    "LastOrderYear": 2004,
    "ProductLine": "Mountain",
    "AddressLine1": "Viktoria-Luise-Platz 475",
    "AddressLine2": "56 Pitt Street",
    "AnnualSales": 3000000,
    "BankName": "International Bank",
    "MinPaymentType": 1,
    "MinPaymentAmount": 600,
    "AnnualRevenue": 300000,
    "YearOpened": 1993
}
2.1.2.15.6 DimReseller Target Script
IF NOT EXISTS (SELECT * FROM sys.databases WHERE name = N'[hacksqlpool]')
begin
	CREATE DATABASE [hacksqlpool];
	USE [hacksqlpool];
end;

CREATE SCHEMA [dbo];

CREATE TABLE [dbo].[DimReseller] (
	[ResellerKey] INT PRIMARY KEY NONCLUSTERED NOT ENFORCED NOT NULL,
	[GeographyKey] INT,
	[ResellerAlternateKey] NVARCHAR(15),
	[Phone] NVARCHAR(25),
	[BusinessType] VARCHAR(20) NOT NULL,
	[ResellerName] NVARCHAR(50) NOT NULL,
	[NumberEmployees] INT,
	[OrderFrequency] CHAR(1),
	[OrderMonth] TINYINT,
	[FirstOrderYear] INT,
	[LastOrderYear] INT,
	[ProductLine] NVARCHAR(50),
	[AddressLine1] NVARCHAR(60),
	[AddressLine2] NVARCHAR(60),
	[AnnualSales] MONEY,
	[BankName] NVARCHAR(50),
	[MinPaymentType] TINYINT,
	[MinPaymentAmount] MONEY,
	[AnnualRevenue] MONEY,
	[YearOpened] INT
)WITH (
	CLUSTERED COLUMNSTORE INDEX,
	DISTRIBUTION = ROUND_ROBIN
);
2.1.2.16 Table DimSalesReason
2.1.2.16.1 DimSalesReason Properties
PropertyValue
TableDimSalesReason
Technical name
Activatedtrue
Id
Schemadbo
Additional propertiesfalse
$ref
Comments
If not exist
Distributionround_robin
Persistenceregular
SQL pool indexingclustered columnstore index
Partition
CTAS query
Range for values
Boundary value
Remarks
2.1.2.16.2 DimSalesReason Column
ColumnTypeReqKeyDescriptionComments
SalesReasonKeyinttruepk, dk
SalesReasonAlternateKeyinttrue
SalesReasonNamenvarchar(50)true
SalesReasonReasonTypenvarchar(50)true
2.1.2.16.2.1 Column SalesReasonKey
2.1.2.16.2.1.1 SalesReasonKey properties
PropertyValue
Business Name SalesReasonKey
Technical name
Activated true
Id
Type numeric
Subtype int
Default
Default constraint name
Comments
Not null true
Masked with function
Identity
Primary key true
Foreign table
Foreign field
Relationship type
Relationship name
Cardinality
Unit
Min value
Excl min
Max value
Excl max
Multiple of
Divisible by
Pattern
Enum
Faker function
Sample 4
Remarks
2.1.2.16.2.2 Column SalesReasonAlternateKey
2.1.2.16.2.2.1 SalesReasonAlternateKey properties
PropertyValue
Business Name SalesReasonAlternateKey
Technical name
Activated true
Id
Type numeric
Subtype int
Default
Default constraint name
Comments
Not null true
Masked with function
Identity
Primary key
Unique
Foreign table
Foreign field
Relationship type
Relationship name
Cardinality
Unit
Min value
Excl min
Max value
Excl max
Multiple of
Divisible by
Pattern
Enum
Faker function
Sample 4
Remarks
2.1.2.16.2.3 Column SalesReasonName
2.1.2.16.2.3.1 SalesReasonName properties
PropertyValue
Business Name SalesReasonName
Technical name
Activated true
Id
Type char
Subtype nvarchar
Length 50
Default
Collate
Default constraint name
Comments
Not null true
Masked with function
Primary key
Unique
Foreign table
Foreign field
Relationship type
Relationship name
Cardinality
Min length
Pattern
Format
Enum
Faker function
Sample Television Advertisement
Remarks
2.1.2.16.2.4 Column SalesReasonReasonType
2.1.2.16.2.4.1 SalesReasonReasonType properties
PropertyValue
Business Name SalesReasonReasonType
Technical name
Activated true
Id
Type char
Subtype nvarchar
Length 50
Default
Collate
Default constraint name
Comments
Not null true
Masked with function
Primary key
Unique
Foreign table
Foreign field
Relationship type
Relationship name
Cardinality
Min length
Pattern
Format
Enum
Faker function
Sample Marketing
Remarks
2.1.2.16.3 DimSalesReason Composite keys
PropertyValue
Primary key
[1] Constraint name
Key
Unique key
[1] Constraint name
Key
Alternate key
2.1.2.16.4 DimSalesReason JSON Schema
{
    "$schema": "http://json-schema.org/draft-04/schema#",
    "type": "object",
    "title": "DimSalesReason",
    "additionalProperties": false,
    "properties": {
        "SalesReasonKey": {
            "type": "number"
        },
        "SalesReasonAlternateKey": {
            "type": "number"
        },
        "SalesReasonName": {
            "type": "string"
        },
        "SalesReasonReasonType": {
            "type": "string"
        }
    },
    "required": [
        "SalesReasonKey",
        "SalesReasonAlternateKey",
        "SalesReasonName",
        "SalesReasonReasonType"
    ]
}
2.1.2.16.5 DimSalesReason JSON data
{
    "SalesReasonKey": 4,
    "SalesReasonAlternateKey": 4,
    "SalesReasonName": "Television  Advertisement",
    "SalesReasonReasonType": "Marketing"
}
2.1.2.16.6 DimSalesReason Target Script
IF NOT EXISTS (SELECT * FROM sys.databases WHERE name = N'[hacksqlpool]')
begin
	CREATE DATABASE [hacksqlpool];
	USE [hacksqlpool];
end;

CREATE SCHEMA [dbo];

CREATE TABLE [dbo].[DimSalesReason] (
	[SalesReasonKey] INT PRIMARY KEY NONCLUSTERED NOT ENFORCED NOT NULL,
	[SalesReasonAlternateKey] INT NOT NULL,
	[SalesReasonName] NVARCHAR(50) NOT NULL,
	[SalesReasonReasonType] NVARCHAR(50) NOT NULL
)WITH (
	CLUSTERED COLUMNSTORE INDEX,
	DISTRIBUTION = ROUND_ROBIN
);
2.1.2.17 Table DimSalesTerritory
2.1.2.17.1 DimSalesTerritory Properties
PropertyValue
TableDimSalesTerritory
Technical name
Activatedtrue
Id
Schemadbo
Additional propertiesfalse
$ref
Comments
If not exist
Distributionround_robin
Persistenceregular
SQL pool indexingclustered columnstore index
Partition
CTAS query
Range for values
Boundary value
Remarks
2.1.2.17.2 DimSalesTerritory Column
ColumnTypeReqKeyDescriptionComments
SalesTerritoryKeyinttruepk, dk
SalesTerritoryAlternateKeyintfalse
SalesTerritoryRegionnvarchar(50)true
SalesTerritoryCountrynvarchar(50)true
SalesTerritoryGroupnvarchar(50)false
2.1.2.17.2.1 Column SalesTerritoryKey
2.1.2.17.2.1.1 SalesTerritoryKey properties
PropertyValue
Business Name SalesTerritoryKey
Technical name
Activated true
Id
Type numeric
Subtype int
Default
Default constraint name
Comments
Not null true
Masked with function
Identity
Primary key true
Foreign table
Foreign field
Relationship type
Relationship name
Cardinality
Unit
Min value
Excl min
Max value
Excl max
Multiple of
Divisible by
Pattern
Enum
Faker function
Sample 4
Remarks
2.1.2.17.2.2 Column SalesTerritoryAlternateKey
2.1.2.17.2.2.1 SalesTerritoryAlternateKey properties
PropertyValue
Business Name SalesTerritoryAlternateKey
Technical name
Activated true
Id
Type numeric
Subtype int
Default
Default constraint name
Comments
Not null
Masked with function
Identity
Primary key
Unique
Foreign table
Foreign field
Relationship type
Relationship name
Cardinality
Unit
Min value
Excl min
Max value
Excl max
Multiple of
Divisible by
Pattern
Enum
Faker function
Sample 4
Remarks
2.1.2.17.2.3 Column SalesTerritoryRegion
2.1.2.17.2.3.1 SalesTerritoryRegion properties
PropertyValue
Business Name SalesTerritoryRegion
Technical name
Activated true
Id
Type char
Subtype nvarchar
Length 50
Default
Collate
Default constraint name
Comments
Not null true
Masked with function
Primary key
Unique
Foreign table
Foreign field
Relationship type
Relationship name
Cardinality
Min length
Pattern
Format
Enum
Faker function
Sample Southwest
Remarks
2.1.2.17.2.4 Column SalesTerritoryCountry
2.1.2.17.2.4.1 SalesTerritoryCountry properties
PropertyValue
Business Name SalesTerritoryCountry
Technical name
Activated true
Id
Type char
Subtype nvarchar
Length 50
Default
Collate
Default constraint name
Comments
Not null true
Masked with function
Primary key
Unique
Foreign table
Foreign field
Relationship type
Relationship name
Cardinality
Min length
Pattern
Format
Enum
Faker function
Sample United States
Remarks
2.1.2.17.2.5 Column SalesTerritoryGroup
2.1.2.17.2.5.1 SalesTerritoryGroup properties
PropertyValue
Business Name SalesTerritoryGroup
Technical name
Activated true
Id
Type char
Subtype nvarchar
Length 50
Default
Collate
Default constraint name
Comments
Not null
Masked with function
Primary key
Unique
Foreign table
Foreign field
Relationship type
Relationship name
Cardinality
Min length
Pattern
Format
Enum
Faker function
Sample North America
Remarks
2.1.2.17.3 DimSalesTerritory Composite keys
PropertyValue
Primary key
[1] Constraint name
Key
Unique key
[1] Constraint name
Key
Alternate key
2.1.2.17.4 DimSalesTerritory JSON Schema
{
    "$schema": "http://json-schema.org/draft-04/schema#",
    "type": "object",
    "title": "DimSalesTerritory",
    "additionalProperties": false,
    "properties": {
        "SalesTerritoryKey": {
            "type": "number"
        },
        "SalesTerritoryAlternateKey": {
            "type": "number"
        },
        "SalesTerritoryRegion": {
            "type": "string"
        },
        "SalesTerritoryCountry": {
            "type": "string"
        },
        "SalesTerritoryGroup": {
            "type": "string"
        }
    },
    "required": [
        "SalesTerritoryKey",
        "SalesTerritoryRegion",
        "SalesTerritoryCountry"
    ]
}
2.1.2.17.5 DimSalesTerritory JSON data
{
    "SalesTerritoryKey": 4,
    "SalesTerritoryAlternateKey": 4,
    "SalesTerritoryRegion": "Southwest",
    "SalesTerritoryCountry": "United States",
    "SalesTerritoryGroup": "North America"
}
2.1.2.17.6 DimSalesTerritory Target Script
IF NOT EXISTS (SELECT * FROM sys.databases WHERE name = N'[hacksqlpool]')
begin
	CREATE DATABASE [hacksqlpool];
	USE [hacksqlpool];
end;

CREATE SCHEMA [dbo];

CREATE TABLE [dbo].[DimSalesTerritory] (
	[SalesTerritoryKey] INT PRIMARY KEY NONCLUSTERED NOT ENFORCED NOT NULL,
	[SalesTerritoryAlternateKey] INT,
	[SalesTerritoryRegion] NVARCHAR(50) NOT NULL,
	[SalesTerritoryCountry] NVARCHAR(50) NOT NULL,
	[SalesTerritoryGroup] NVARCHAR(50)
)WITH (
	CLUSTERED COLUMNSTORE INDEX,
	DISTRIBUTION = ROUND_ROBIN
);
2.1.2.18 Table DimScenario
2.1.2.18.1 DimScenario Properties
PropertyValue
TableDimScenario
Technical name
Activatedtrue
Id
Schemadbo
Additional propertiesfalse
$ref
Comments
If not exist
Distributionround_robin
Persistenceregular
SQL pool indexingclustered columnstore index
Partition
CTAS query
Range for values
Boundary value
Remarks
2.1.2.18.2 DimScenario Column
ColumnTypeReqKeyDescriptionComments
ScenarioKeyinttruepk, dk
ScenarioNamenvarchar(50)false
2.1.2.18.2.1 Column ScenarioKey
2.1.2.18.2.1.1 ScenarioKey properties
PropertyValue
Business Name ScenarioKey
Technical name
Activated true
Id
Type numeric
Subtype int
Default
Default constraint name
Comments
Not null true
Masked with function
Identity
Primary key true
Foreign table
Foreign field
Relationship type
Relationship name
Cardinality
Unit
Min value
Excl min
Max value
Excl max
Multiple of
Divisible by
Pattern
Enum
Faker function
Sample 3
Remarks
2.1.2.18.2.2 Column ScenarioName
2.1.2.18.2.2.1 ScenarioName properties
PropertyValue
Business Name ScenarioName
Technical name
Activated true
Id
Type char
Subtype nvarchar
Length 50
Default
Collate
Default constraint name
Comments
Not null
Masked with function
Primary key
Unique
Foreign table
Foreign field
Relationship type
Relationship name
Cardinality
Min length
Pattern
Format
Enum
Faker function
Sample Forecast
Remarks
2.1.2.18.3 DimScenario Composite keys
PropertyValue
Primary key
[1] Constraint name
Key
Unique key
[1] Constraint name
Key
Alternate key
2.1.2.18.4 DimScenario JSON Schema
{
    "$schema": "http://json-schema.org/draft-04/schema#",
    "type": "object",
    "title": "DimScenario",
    "additionalProperties": false,
    "properties": {
        "ScenarioKey": {
            "type": "number"
        },
        "ScenarioName": {
            "type": "string"
        }
    },
    "required": [
        "ScenarioKey"
    ]
}
2.1.2.18.5 DimScenario JSON data
{
    "ScenarioKey": 3,
    "ScenarioName": "Forecast"
}
2.1.2.18.6 DimScenario Target Script
IF NOT EXISTS (SELECT * FROM sys.databases WHERE name = N'[hacksqlpool]')
begin
	CREATE DATABASE [hacksqlpool];
	USE [hacksqlpool];
end;

CREATE SCHEMA [dbo];

CREATE TABLE [dbo].[DimScenario] (
	[ScenarioKey] INT PRIMARY KEY NONCLUSTERED NOT ENFORCED NOT NULL,
	[ScenarioName] NVARCHAR(50)
)WITH (
	CLUSTERED COLUMNSTORE INDEX,
	DISTRIBUTION = ROUND_ROBIN
);
2.1.2.19 Table FactCallCenter
2.1.2.19.1 FactCallCenter Properties
PropertyValue
TableFactCallCenter
Technical name
Activatedtrue
Id
Schemadbo
Additional propertiesfalse
$ref
Comments
If not exist
Distributionround_robin
Persistenceregular
SQL pool indexingclustered columnstore index
Partition
CTAS query
Range for values
Boundary value
Remarks
2.1.2.19.2 FactCallCenter Column
ColumnTypeReqKeyDescriptionComments
FactCallCenterIDinttruepk
DateKeyinttruefk
WageTypenvarchar(15)true
Shiftnvarchar(20)true
LevelOneOperatorssmallinttrue
LevelTwoOperatorssmallinttrue
TotalOperatorssmallinttrue
Callsinttrue
AutomaticResponsesinttrue
Ordersinttrue
IssuesRaisedsmallinttrue
AverageTimePerIssuesmallinttrue
ServiceGradefloattrue
2.1.2.19.2.1 Column FactCallCenterID
2.1.2.19.2.1.1 FactCallCenterID properties
PropertyValue
Business Name FactCallCenterID
Technical name
Activated true
Id
Type numeric
Subtype int
Default
Default constraint name
Comments
Not null true
Masked with function
Identity
Primary key true
Foreign table
Foreign field
Relationship type
Relationship name
Cardinality
Unit
Min value
Excl min
Max value
Excl max
Multiple of
Divisible by
Pattern
Enum
Faker function
Sample 101
Remarks
2.1.2.19.2.2 Column DateKey
2.1.2.19.2.2.1 DateKey properties
PropertyValue
Business Name DateKey
Technical name
Activated true
Id
Type numeric
Subtype int
Default
Default constraint name
Comments
Not null true
Masked with function
Identity
Primary key
Unique
Foreign table DimDate
Foreign field DateKey
Relationship type Foreign Key
Relationship name fk DimDate.DateKey to FactCallCenter.DateKey
Cardinality 0..n
Unit
Min value
Excl min
Max value
Excl max
Multiple of
Divisible by
Pattern
Enum
Faker function
Sample 20061126
Remarks
2.1.2.19.2.3 Column WageType
2.1.2.19.2.3.1 WageType properties
PropertyValue
Business Name WageType
Technical name
Activated true
Id
Type char
Subtype nvarchar
Length 15
Default
Collate
Default constraint name
Comments
Not null true
Masked with function
Primary key
Unique
Foreign table
Foreign field
Relationship type
Relationship name
Cardinality
Min length
Pattern
Format
Enum
Faker function
Sample holiday
Remarks
2.1.2.19.2.4 Column Shift
2.1.2.19.2.4.1 Shift properties
PropertyValue
Business Name Shift
Technical name
Activated true
Id
Type char
Subtype nvarchar
Length 20
Default
Collate
Default constraint name
Comments
Not null true
Masked with function
Primary key
Unique
Foreign table
Foreign field
Relationship type
Relationship name
Cardinality
Min length
Pattern
Format
Enum
Faker function
Sample AM
Remarks
2.1.2.19.2.5 Column LevelOneOperators
2.1.2.19.2.5.1 LevelOneOperators properties
PropertyValue
Business Name LevelOneOperators
Technical name
Activated true
Id
Type numeric
Subtype smallint
Default
Default constraint name
Comments
Not null true
Masked with function
Identity
Primary key
Unique
Foreign table
Foreign field
Relationship type
Relationship name
Cardinality
Unit
Min value
Excl min
Max value
Excl max
Multiple of
Divisible by
Pattern
Enum
Faker function
Sample 2
Remarks
2.1.2.19.2.6 Column LevelTwoOperators
2.1.2.19.2.6.1 LevelTwoOperators properties
PropertyValue
Business Name LevelTwoOperators
Technical name
Activated true
Id
Type numeric
Subtype smallint
Default
Default constraint name
Comments
Not null true
Masked with function
Identity
Primary key
Unique
Foreign table
Foreign field
Relationship type
Relationship name
Cardinality
Unit
Min value
Excl min
Max value
Excl max
Multiple of
Divisible by
Pattern
Enum
Faker function
Sample 8
Remarks
2.1.2.19.2.7 Column TotalOperators
2.1.2.19.2.7.1 TotalOperators properties
PropertyValue
Business Name TotalOperators
Technical name
Activated true
Id
Type numeric
Subtype smallint
Default
Default constraint name
Comments
Not null true
Masked with function
Identity
Primary key
Unique
Foreign table
Foreign field
Relationship type
Relationship name
Cardinality
Unit
Min value
Excl min
Max value
Excl max
Multiple of
Divisible by
Pattern
Enum
Faker function
Sample 10
Remarks
2.1.2.19.2.8 Column Calls
2.1.2.19.2.8.1 Calls properties
PropertyValue
Business Name Calls
Technical name
Activated true
Id
Type numeric
Subtype int
Default
Default constraint name
Comments
Not null true
Masked with function
Identity
Primary key
Unique
Foreign table
Foreign field
Relationship type
Relationship name
Cardinality
Unit
Min value
Excl min
Max value
Excl max
Multiple of
Divisible by
Pattern
Enum
Faker function
Sample 278
Remarks
2.1.2.19.2.9 Column AutomaticResponses
2.1.2.19.2.9.1 AutomaticResponses properties
PropertyValue
Business Name AutomaticResponses
Technical name
Activated true
Id
Type numeric
Subtype int
Default
Default constraint name
Comments
Not null true
Masked with function
Identity
Primary key
Unique
Foreign table
Foreign field
Relationship type
Relationship name
Cardinality
Unit
Min value
Excl min
Max value
Excl max
Multiple of
Divisible by
Pattern
Enum
Faker function
Sample 180
Remarks
2.1.2.19.2.10 Column Orders
2.1.2.19.2.10.1 Orders properties
PropertyValue
Business Name Orders
Technical name
Activated true
Id
Type numeric
Subtype int
Default
Default constraint name
Comments
Not null true
Masked with function
Identity
Primary key
Unique
Foreign table
Foreign field
Relationship type
Relationship name
Cardinality
Unit
Min value
Excl min
Max value
Excl max
Multiple of
Divisible by
Pattern
Enum
Faker function
Sample 194
Remarks
2.1.2.19.2.11 Column IssuesRaised
2.1.2.19.2.11.1 IssuesRaised properties
PropertyValue
Business Name IssuesRaised
Technical name
Activated true
Id
Type numeric
Subtype smallint
Default
Default constraint name
Comments
Not null true
Masked with function
Identity
Primary key
Unique
Foreign table
Foreign field
Relationship type
Relationship name
Cardinality
Unit
Min value
Excl min
Max value
Excl max
Multiple of
Divisible by
Pattern
Enum
Faker function
Sample 2
Remarks
2.1.2.19.2.12 Column AverageTimePerIssue
2.1.2.19.2.12.1 AverageTimePerIssue properties
PropertyValue
Business Name AverageTimePerIssue
Technical name
Activated true
Id
Type numeric
Subtype smallint
Default
Default constraint name
Comments
Not null true
Masked with function
Identity
Primary key
Unique
Foreign table
Foreign field
Relationship type
Relationship name
Cardinality
Unit
Min value
Excl min
Max value
Excl max
Multiple of
Divisible by
Pattern
Enum
Faker function
Sample 62
Remarks
2.1.2.19.2.13 Column ServiceGrade
2.1.2.19.2.13.1 ServiceGrade properties
PropertyValue
Business Name ServiceGrade
Technical name
Activated true
Id
Type numeric
Subtype float
Default
Default constraint name
Comments
Not null true
Masked with function
Identity
Primary key
Unique
Foreign table
Foreign field
Relationship type
Relationship name
Cardinality
Unit
Min value
Excl min
Max value
Excl max
Multiple of
Divisible by
Pattern
Enum
Faker function
Sample 0.07
Remarks
2.1.2.19.3 FactCallCenter Composite keys
PropertyValue
Primary key
[1] Constraint name
Key
Unique key
[1] Constraint name
Key
Alternate key
2.1.2.19.4 FactCallCenter JSON Schema
{
    "$schema": "http://json-schema.org/draft-04/schema#",
    "type": "object",
    "title": "FactCallCenter",
    "additionalProperties": false,
    "properties": {
        "FactCallCenterID": {
            "type": "number"
        },
        "DateKey": {
            "type": "number"
        },
        "WageType": {
            "type": "string"
        },
        "Shift": {
            "type": "string"
        },
        "LevelOneOperators": {
            "type": "number"
        },
        "LevelTwoOperators": {
            "type": "number"
        },
        "TotalOperators": {
            "type": "number"
        },
        "Calls": {
            "type": "number"
        },
        "AutomaticResponses": {
            "type": "number"
        },
        "Orders": {
            "type": "number"
        },
        "IssuesRaised": {
            "type": "number"
        },
        "AverageTimePerIssue": {
            "type": "number"
        },
        "ServiceGrade": {
            "type": "number"
        }
    },
    "required": [
        "FactCallCenterID",
        "DateKey",
        "WageType",
        "Shift",
        "LevelOneOperators",
        "LevelTwoOperators",
        "TotalOperators",
        "Calls",
        "AutomaticResponses",
        "Orders",
        "IssuesRaised",
        "AverageTimePerIssue",
        "ServiceGrade"
    ]
}
2.1.2.19.5 FactCallCenter JSON data
{
    "FactCallCenterID": 101,
    "DateKey": 20061126,
    "WageType": "holiday",
    "Shift": "AM",
    "LevelOneOperators": 2,
    "LevelTwoOperators": 8,
    "TotalOperators": 10,
    "Calls": 278,
    "AutomaticResponses": 180,
    "Orders": 194,
    "IssuesRaised": 2,
    "AverageTimePerIssue": 62,
    "ServiceGrade": 0.07
}
2.1.2.19.6 FactCallCenter Target Script
IF NOT EXISTS (SELECT * FROM sys.databases WHERE name = N'[hacksqlpool]')
begin
	CREATE DATABASE [hacksqlpool];
	USE [hacksqlpool];
end;

CREATE SCHEMA [dbo];

CREATE TABLE [dbo].[FactCallCenter] (
	[FactCallCenterID] INT PRIMARY KEY NONCLUSTERED NOT ENFORCED NOT NULL,
	[DateKey] INT NOT NULL,
	[WageType] NVARCHAR(15) NOT NULL,
	[Shift] NVARCHAR(20) NOT NULL,
	[LevelOneOperators] SMALLINT NOT NULL,
	[LevelTwoOperators] SMALLINT NOT NULL,
	[TotalOperators] SMALLINT NOT NULL,
	[Calls] INT NOT NULL,
	[AutomaticResponses] INT NOT NULL,
	[Orders] INT NOT NULL,
	[IssuesRaised] SMALLINT NOT NULL,
	[AverageTimePerIssue] SMALLINT NOT NULL,
	[ServiceGrade] FLOAT NOT NULL
)WITH (
	CLUSTERED COLUMNSTORE INDEX,
	DISTRIBUTION = ROUND_ROBIN
);
2.1.2.20 Table FactCurrencyRate
2.1.2.20.1 FactCurrencyRate Properties
PropertyValue
TableFactCurrencyRate
Technical name
Activatedtrue
Id
Schemadbo
Additional propertiesfalse
$ref
Comments
If not exist
Distributionround_robin
Persistenceregular
SQL pool indexingclustered columnstore index
Partition
CTAS query
Range for values
Boundary value
Remarks
2.1.2.20.2 FactCurrencyRate Column
ColumnTypeReqKeyDescriptionComments
CurrencyKeyinttruepk, fk
DateKeyinttruefk
AverageRatefloattrue
EndOfDayRatefloattrue
2.1.2.20.2.1 Column CurrencyKey
2.1.2.20.2.1.1 CurrencyKey properties
PropertyValue
Business Name CurrencyKey
Technical name
Activated true
Id
Type numeric
Subtype int
Default
Default constraint name
Comments
Not null true
Masked with function
Identity
Primary key true
Foreign table DimCurrency
Foreign field CurrencyKey
Relationship type Foreign Key
Relationship name fk DimCurrency.CurrencyKey to FactCurrencyRate.CurrencyKey
Cardinality 0..n
Unit
Min value
Excl min
Max value
Excl max
Multiple of
Divisible by
Pattern
Enum
Faker function
Sample 103
Remarks
2.1.2.20.2.2 Column DateKey
2.1.2.20.2.2.1 DateKey properties
PropertyValue
Business Name DateKey
Technical name
Activated true
Id
Type numeric
Subtype int
Default
Default constraint name
Comments
Not null true
Masked with function
Identity
Primary key
Unique
Foreign table DimDate
Foreign field DateKey
Relationship type Foreign Key
Relationship name fk DimDate.DateKey to FactCurrencyRate.DateKey
Cardinality 0..n
Unit
Min value
Excl min
Max value
Excl max
Multiple of
Divisible by
Pattern
Enum
Faker function
Sample 20010922
Remarks
2.1.2.20.2.3 Column AverageRate
2.1.2.20.2.3.1 AverageRate properties
PropertyValue
Business Name AverageRate
Technical name
Activated true
Id
Type numeric
Subtype float
Default
Default constraint name
Comments
Not null true
Masked with function
Identity
Primary key
Unique
Foreign table
Foreign field
Relationship type
Relationship name
Cardinality
Unit
Min value
Excl min
Max value
Excl max
Multiple of
Divisible by
Pattern
Enum
Faker function
Sample 0.120778781583651
Remarks
2.1.2.20.2.4 Column EndOfDayRate
2.1.2.20.2.4.1 EndOfDayRate properties
PropertyValue
Business Name EndOfDayRate
Technical name
Activated true
Id
Type numeric
Subtype float
Default
Default constraint name
Comments
Not null true
Masked with function
Identity
Primary key
Unique
Foreign table
Foreign field
Relationship type
Relationship name
Cardinality
Unit
Min value
Excl min
Max value
Excl max
Multiple of
Divisible by
Pattern
Enum
Faker function
Sample 0.120786075781184
Remarks
2.1.2.20.3 FactCurrencyRate Composite keys
PropertyValue
Primary key
[1] Constraint name
Key
Unique key
[1] Constraint name
Key
Alternate key
2.1.2.20.4 FactCurrencyRate JSON Schema
{
    "$schema": "http://json-schema.org/draft-04/schema#",
    "type": "object",
    "title": "FactCurrencyRate",
    "additionalProperties": false,
    "properties": {
        "CurrencyKey": {
            "type": "number"
        },
        "DateKey": {
            "type": "number"
        },
        "AverageRate": {
            "type": "number"
        },
        "EndOfDayRate": {
            "type": "number"
        }
    },
    "required": [
        "CurrencyKey",
        "DateKey",
        "AverageRate",
        "EndOfDayRate"
    ]
}
2.1.2.20.5 FactCurrencyRate JSON data
{
    "CurrencyKey": 103,
    "DateKey": 20010922,
    "AverageRate": 0.120778781583651,
    "EndOfDayRate": 0.120786075781184
}
2.1.2.20.6 FactCurrencyRate Target Script
IF NOT EXISTS (SELECT * FROM sys.databases WHERE name = N'[hacksqlpool]')
begin
	CREATE DATABASE [hacksqlpool];
	USE [hacksqlpool];
end;

CREATE SCHEMA [dbo];

CREATE TABLE [dbo].[FactCurrencyRate] (
	[CurrencyKey] INT PRIMARY KEY NONCLUSTERED NOT ENFORCED NOT NULL,
	[DateKey] INT NOT NULL,
	[AverageRate] FLOAT NOT NULL,
	[EndOfDayRate] FLOAT NOT NULL
)WITH (
	CLUSTERED COLUMNSTORE INDEX,
	DISTRIBUTION = ROUND_ROBIN
);
2.1.2.21 Table FactFinance
2.1.2.21.1 FactFinance Properties
PropertyValue
TableFactFinance
Technical name
Activatedtrue
Id
Schemadbo
Additional propertiesfalse
$ref
Comments
If not exist
Distributionround_robin
Persistenceregular
SQL pool indexingclustered columnstore index
Partition
CTAS query
Range for values
Boundary value
Remarks
2.1.2.21.2 FactFinance Column
ColumnTypeReqKeyDescriptionComments
FinanceKeyinttruepk
DateKeyinttruefk
OrganizationKeyinttruefk
DepartmentGroupKeyinttruefk
ScenarioKeyinttruefk
AccountKeyinttruefk
Amountfloattrue
2.1.2.21.2.1 Column FinanceKey
2.1.2.21.2.1.1 FinanceKey properties
PropertyValue
Business Name FinanceKey
Technical name
Activated true
Id
Type numeric
Subtype int
Default
Default constraint name
Comments
Not null true
Masked with function
Identity
Primary key true
Foreign table
Foreign field
Relationship type
Relationship name
Cardinality
Unit
Min value
Excl min
Max value
Excl max
Multiple of
Divisible by
Pattern
Enum
Faker function
Sample 33918
Remarks
2.1.2.21.2.2 Column DateKey
2.1.2.21.2.2.1 DateKey properties
PropertyValue
Business Name DateKey
Technical name
Activated true
Id
Type numeric
Subtype int
Default
Default constraint name
Comments
Not null true
Masked with function
Identity
Primary key
Unique
Foreign table DimDate
Foreign field DateKey
Relationship type Foreign Key
Relationship name fk DimDate.DateKey to FactFinance.DateKey
Cardinality 0..n
Unit
Min value
Excl min
Max value
Excl max
Multiple of
Divisible by
Pattern
Enum
Faker function
Sample 20040201
Remarks
2.1.2.21.2.3 Column OrganizationKey
2.1.2.21.2.3.1 OrganizationKey properties
PropertyValue
Business Name OrganizationKey
Technical name
Activated true
Id
Type numeric
Subtype int
Default
Default constraint name
Comments
Not null true
Masked with function
Identity
Primary key
Unique
Foreign table DimOrganization
Foreign field OrganizationKey
Relationship type Foreign Key
Relationship name fk DimOrganization.OrganizationKey to FactFinance.OrganizationKey
Cardinality 0..n
Unit
Min value
Excl min
Max value
Excl max
Multiple of
Divisible by
Pattern
Enum
Faker function
Sample 5
Remarks
2.1.2.21.2.4 Column DepartmentGroupKey
2.1.2.21.2.4.1 DepartmentGroupKey properties
PropertyValue
Business Name DepartmentGroupKey
Technical name
Activated true
Id
Type numeric
Subtype int
Default
Default constraint name
Comments
Not null true
Masked with function
Identity
Primary key
Unique
Foreign table DimDepartmentGroup
Foreign field DepartmentGroupKey
Relationship type Foreign Key
Relationship name fk DimDepartmentGroup.DepartmentGroupKey to FactFinance.DepartmentGroupKey
Cardinality 0..n
Unit
Min value
Excl min
Max value
Excl max
Multiple of
Divisible by
Pattern
Enum
Faker function
Sample 7
Remarks
2.1.2.21.2.5 Column ScenarioKey
2.1.2.21.2.5.1 ScenarioKey properties
PropertyValue
Business Name ScenarioKey
Technical name
Activated true
Id
Type numeric
Subtype int
Default
Default constraint name
Comments
Not null true
Masked with function
Identity
Primary key
Unique
Foreign table DimScenario
Foreign field ScenarioKey
Relationship type Foreign Key
Relationship name fk DimScenario.ScenarioKey to FactFinance.ScenarioKey
Cardinality 0..n
Unit
Min value
Excl min
Max value
Excl max
Multiple of
Divisible by
Pattern
Enum
Faker function
Sample 1
Remarks
2.1.2.21.2.6 Column AccountKey
2.1.2.21.2.6.1 AccountKey properties
PropertyValue
Business Name AccountKey
Technical name
Activated true
Id
Type numeric
Subtype int
Default
Default constraint name
Comments
Not null true
Masked with function
Identity
Primary key
Unique
Foreign table DimAccount
Foreign field AccountKey
Relationship type Foreign Key
Relationship name fk DimAccount.AccountKey to FactFinance.AccountKey
Cardinality 0..n
Unit
Min value
Excl min
Max value
Excl max
Multiple of
Divisible by
Pattern
Enum
Faker function
Sample 33
Remarks
2.1.2.21.2.7 Column Amount
2.1.2.21.2.7.1 Amount properties
PropertyValue
Business Name Amount
Technical name
Activated true
Id
Type numeric
Subtype float
Default
Default constraint name
Comments
Not null true
Masked with function
Identity
Primary key
Unique
Foreign table
Foreign field
Relationship type
Relationship name
Cardinality
Unit
Min value
Excl min
Max value
Excl max
Multiple of
Divisible by
Pattern
Enum
Faker function
Sample 29634
Remarks
2.1.2.21.3 FactFinance Composite keys
PropertyValue
Primary key
[1] Constraint name
Key
Unique key
[1] Constraint name
Key
Alternate key
2.1.2.21.4 FactFinance JSON Schema
{
    "$schema": "http://json-schema.org/draft-04/schema#",
    "type": "object",
    "title": "FactFinance",
    "additionalProperties": false,
    "properties": {
        "FinanceKey": {
            "type": "number"
        },
        "DateKey": {
            "type": "number"
        },
        "OrganizationKey": {
            "type": "number"
        },
        "DepartmentGroupKey": {
            "type": "number"
        },
        "ScenarioKey": {
            "type": "number"
        },
        "AccountKey": {
            "type": "number"
        },
        "Amount": {
            "type": "number"
        }
    },
    "required": [
        "FinanceKey",
        "DateKey",
        "OrganizationKey",
        "DepartmentGroupKey",
        "ScenarioKey",
        "AccountKey",
        "Amount"
    ]
}
2.1.2.21.5 FactFinance JSON data
{
    "FinanceKey": 33918,
    "DateKey": 20040201,
    "OrganizationKey": 5,
    "DepartmentGroupKey": 7,
    "ScenarioKey": 1,
    "AccountKey": 33,
    "Amount": 29634
}
2.1.2.21.6 FactFinance Target Script
IF NOT EXISTS (SELECT * FROM sys.databases WHERE name = N'[hacksqlpool]')
begin
	CREATE DATABASE [hacksqlpool];
	USE [hacksqlpool];
end;

CREATE SCHEMA [dbo];

CREATE TABLE [dbo].[FactFinance] (
	[FinanceKey] INT PRIMARY KEY NONCLUSTERED NOT ENFORCED NOT NULL,
	[DateKey] INT NOT NULL,
	[OrganizationKey] INT NOT NULL,
	[DepartmentGroupKey] INT NOT NULL,
	[ScenarioKey] INT NOT NULL,
	[AccountKey] INT NOT NULL,
	[Amount] FLOAT NOT NULL
)WITH (
	CLUSTERED COLUMNSTORE INDEX,
	DISTRIBUTION = ROUND_ROBIN
);
2.1.2.22 Table FactInternetSales
2.1.2.22.1 FactInternetSales Properties
PropertyValue
TableFactInternetSales
Technical name
Activatedtrue
Id
Schemadbo
Additional propertiesfalse
$ref
Comments
If not exist
Distributionround_robin
Persistenceregular
SQL pool indexingclustered columnstore index
Partition
CTAS query
Range for values
Boundary value
Remarks
2.1.2.22.2 FactInternetSales Column
ColumnTypeReqKeyDescriptionComments
ProductKeyinttruepk
OrderDateKeyinttrue
DueDateKeyinttruefk
ShipDateKeyinttruefk
CustomerKeyinttruefk
PromotionKeyinttruefk
CurrencyKeyinttruefk
SalesTerritoryKeyinttruefk
SalesOrderNumbernvarchar(20)truefk
SalesOrderLineNumbertinyinttrue
RevisionNumbertinyinttrue
OrderQuantitysmallinttrue
UnitPricemoneytrue
ExtendedAmountmoneytrue
UnitPriceDiscountPctfloattrue
DiscountAmountfloattrue
ProductStandardCostmoneytrue
TotalProductCostmoneytrue
SalesAmountmoneytrue
TaxAmtmoneytrue
Freightmoneytrue
CarrierTrackingNumbernvarchar(25)false
CustomerPONumbernvarchar(25)false
2.1.2.22.2.1 Column ProductKey
2.1.2.22.2.1.1 ProductKey properties
PropertyValue
Business Name ProductKey
Technical name
Activated true
Id
Type numeric
Subtype int
Default
Default constraint name
Comments
Not null true
Masked with function
Identity
Primary key true
Foreign table
Foreign field
Relationship type
Relationship name
Cardinality
Unit
Min value
Excl min
Max value
Excl max
Multiple of
Divisible by
Pattern
Enum
Faker function
Sample 373
Remarks
2.1.2.22.2.2 Column OrderDateKey
2.1.2.22.2.2.1 OrderDateKey properties
PropertyValue
Business Name OrderDateKey
Technical name
Activated true
Id
Type numeric
Subtype int
Default
Default constraint name
Comments
Not null true
Masked with function
Identity
Primary key
Unique
Foreign table
Foreign field
Relationship type
Relationship name
Cardinality
Unit
Min value
Excl min
Max value
Excl max
Multiple of
Divisible by
Pattern
Enum
Faker function
Sample 20020803
Remarks
2.1.2.22.2.3 Column DueDateKey
2.1.2.22.2.3.1 DueDateKey properties
PropertyValue
Business Name DueDateKey
Technical name
Activated true
Id
Type numeric
Subtype int
Default
Default constraint name
Comments
Not null true
Masked with function
Identity
Primary key
Unique
Foreign table DimDate
Foreign field DateKey
Relationship type Foreign Key
Relationship name fk FactInternetSales.DueDateKey to DimDate.DateKey
Cardinality 1..n
Unit
Min value
Excl min
Max value
Excl max
Multiple of
Divisible by
Pattern
Enum
Faker function
Sample 20020815
Remarks
2.1.2.22.2.4 Column ShipDateKey
2.1.2.22.2.4.1 ShipDateKey properties
PropertyValue
Business Name ShipDateKey
Technical name
Activated true
Id
Type numeric
Subtype int
Default
Default constraint name
Comments
Not null true
Masked with function
Identity
Primary key
Unique
Foreign table DimDate
Foreign field DateKey
Relationship type Foreign Key
Relationship name fk FactInternetSales.ShipDateKey to DimDate.DateKey
Cardinality 1..n
Unit
Min value
Excl min
Max value
Excl max
Multiple of
Divisible by
Pattern
Enum
Faker function
Sample 20020810
Remarks
2.1.2.22.2.5 Column CustomerKey
2.1.2.22.2.5.1 CustomerKey properties
PropertyValue
Business Name CustomerKey
Technical name
Activated true
Id
Type numeric
Subtype int
Default
Default constraint name
Comments
Not null true
Masked with function
Identity
Primary key
Unique
Foreign table DimCustomer
Foreign field CustomerKey
Relationship type Foreign Key
Relationship name fk FactInternetSales.CustomerKey to DimCustomer.CustomerKey
Cardinality 1..n
Unit
Min value
Excl min
Max value
Excl max
Multiple of
Divisible by
Pattern
Enum
Faker function
Sample 13378
Remarks
2.1.2.22.2.6 Column PromotionKey
2.1.2.22.2.6.1 PromotionKey properties
PropertyValue
Business Name PromotionKey
Technical name
Activated true
Id
Type numeric
Subtype int
Default
Default constraint name
Comments
Not null true
Masked with function
Identity
Primary key
Unique
Foreign table DimPromotion
Foreign field PromotionKey
Relationship type Foreign Key
Relationship name fk FactInternetSales.PromotionKey to DimPromotion.PromotionKey
Cardinality 1..n
Unit
Min value
Excl min
Max value
Excl max
Multiple of
Divisible by
Pattern
Enum
Faker function
Sample 1
Remarks
2.1.2.22.2.7 Column CurrencyKey
2.1.2.22.2.7.1 CurrencyKey properties
PropertyValue
Business Name CurrencyKey
Technical name
Activated true
Id
Type numeric
Subtype int
Default
Default constraint name
Comments
Not null true
Masked with function
Identity
Primary key
Unique
Foreign table DimCurrency
Foreign field CurrencyKey
Relationship type Foreign Key
Relationship name fk FactInternetSales.CurrencyKey to DimCurrency.CurrencyKey
Cardinality 1..n
Unit
Min value
Excl min
Max value
Excl max
Multiple of
Divisible by
Pattern
Enum
Faker function
Sample 100
Remarks
2.1.2.22.2.8 Column SalesTerritoryKey
2.1.2.22.2.8.1 SalesTerritoryKey properties
PropertyValue
Business Name SalesTerritoryKey
Technical name
Activated true
Id
Type numeric
Subtype int
Default
Default constraint name
Comments
Not null true
Masked with function
Identity
Primary key
Unique
Foreign table DimSalesTerritory
Foreign field SalesTerritoryKey
Relationship type Foreign Key
Relationship name fk FactInternetSales.SalesTerritoryKey to DimSalesTerritory.SalesTerritoryKey
Cardinality 1..n
Unit
Min value
Excl min
Max value
Excl max
Multiple of
Divisible by
Pattern
Enum
Faker function
Sample 4
Remarks
2.1.2.22.2.9 Column SalesOrderNumber
2.1.2.22.2.9.1 SalesOrderNumber properties
PropertyValue
Business Name SalesOrderNumber
Technical name
Activated true
Id
Type char
Subtype nvarchar
Length 20
Default
Collate
Default constraint name
Comments
Not null true
Masked with function
Primary key
Unique
Foreign table FactInternetSalesReason
Foreign field SalesOrderNumber
Relationship type Foreign Key
Relationship name fk FactInternetSales.SalesOrderNumber to FactInternetSalesReason.SalesOrderNumber
Cardinality 1..n
Min length
Pattern
Format
Enum
Faker function
Sample SO47086
Remarks
2.1.2.22.2.10 Column SalesOrderLineNumber
2.1.2.22.2.10.1 SalesOrderLineNumber properties
PropertyValue
Business Name SalesOrderLineNumber
Technical name
Activated true
Id
Type numeric
Subtype tinyint
Default
Default constraint name
Comments
Not null true
Masked with function
Identity
Primary key
Unique
Foreign table
Foreign field
Relationship type
Relationship name
Cardinality
Unit
Min value
Excl min
Max value
Excl max
Multiple of
Divisible by
Pattern
Enum
Faker function
Sample 1
Remarks
2.1.2.22.2.11 Column RevisionNumber
2.1.2.22.2.11.1 RevisionNumber properties
PropertyValue
Business Name RevisionNumber
Technical name
Activated true
Id
Type numeric
Subtype tinyint
Default
Default constraint name
Comments
Not null true
Masked with function
Identity
Primary key
Unique
Foreign table
Foreign field
Relationship type
Relationship name
Cardinality
Unit
Min value
Excl min
Max value
Excl max
Multiple of
Divisible by
Pattern
Enum
Faker function
Sample 1
Remarks
2.1.2.22.2.12 Column OrderQuantity
2.1.2.22.2.12.1 OrderQuantity properties
PropertyValue
Business Name OrderQuantity
Technical name
Activated true
Id
Type numeric
Subtype smallint
Default
Default constraint name
Comments
Not null true
Masked with function
Identity
Primary key
Unique
Foreign table
Foreign field
Relationship type
Relationship name
Cardinality
Unit
Min value
Excl min
Max value
Excl max
Multiple of
Divisible by
Pattern
Enum
Faker function
Sample 1
Remarks
2.1.2.22.2.13 Column UnitPrice
2.1.2.22.2.13.1 UnitPrice properties
PropertyValue
Business Name UnitPrice
Technical name
Activated true
Id
Type numeric
Subtype money
Default
Default constraint name
Comments
Not null true
Masked with function
Identity
Primary key
Unique
Foreign table
Foreign field
Relationship type
Relationship name
Cardinality
Unit
Min value
Excl min
Max value
Excl max
Multiple of
Divisible by
Pattern
Enum
Faker function
Sample 2181.5625
Remarks
2.1.2.22.2.14 Column ExtendedAmount
2.1.2.22.2.14.1 ExtendedAmount properties
PropertyValue
Business Name ExtendedAmount
Technical name
Activated true
Id
Type numeric
Subtype money
Default
Default constraint name
Comments
Not null true
Masked with function
Identity
Primary key
Unique
Foreign table
Foreign field
Relationship type
Relationship name
Cardinality
Unit
Min value
Excl min
Max value
Excl max
Multiple of
Divisible by
Pattern
Enum
Faker function
Sample 2181.5625
Remarks
2.1.2.22.2.15 Column UnitPriceDiscountPct
2.1.2.22.2.15.1 UnitPriceDiscountPct properties
PropertyValue
Business Name UnitPriceDiscountPct
Technical name
Activated true
Id
Type numeric
Subtype float
Default
Default constraint name
Comments
Not null true
Masked with function
Identity
Primary key
Unique
Foreign table
Foreign field
Relationship type
Relationship name
Cardinality
Unit
Min value
Excl min
Max value
Excl max
Multiple of
Divisible by
Pattern
Enum
Faker function
Sample 0
Remarks
2.1.2.22.2.16 Column DiscountAmount
2.1.2.22.2.16.1 DiscountAmount properties
PropertyValue
Business Name DiscountAmount
Technical name
Activated true
Id
Type numeric
Subtype float
Default
Default constraint name
Comments
Not null true
Masked with function
Identity
Primary key
Unique
Foreign table
Foreign field
Relationship type
Relationship name
Cardinality
Unit
Min value
Excl min
Max value
Excl max
Multiple of
Divisible by
Pattern
Enum
Faker function
Sample 0
Remarks
2.1.2.22.2.17 Column ProductStandardCost
2.1.2.22.2.17.1 ProductStandardCost properties
PropertyValue
Business Name ProductStandardCost
Technical name
Activated true
Id
Type numeric
Subtype money
Default
Default constraint name
Comments
Not null true
Masked with function
Identity
Primary key
Unique
Foreign table
Foreign field
Relationship type
Relationship name
Cardinality
Unit
Min value
Excl min
Max value
Excl max
Multiple of
Divisible by
Pattern
Enum
Faker function
Sample 1320.6838
Remarks
2.1.2.22.2.18 Column TotalProductCost
2.1.2.22.2.18.1 TotalProductCost properties
PropertyValue
Business Name TotalProductCost
Technical name
Activated true
Id
Type numeric
Subtype money
Default
Default constraint name
Comments
Not null true
Masked with function
Identity
Primary key
Unique
Foreign table
Foreign field
Relationship type
Relationship name
Cardinality
Unit
Min value
Excl min
Max value
Excl max
Multiple of
Divisible by
Pattern
Enum
Faker function
Sample 1320.6838
Remarks
2.1.2.22.2.19 Column SalesAmount
2.1.2.22.2.19.1 SalesAmount properties
PropertyValue
Business Name SalesAmount
Technical name
Activated true
Id
Type numeric
Subtype money
Default
Default constraint name
Comments
Not null true
Masked with function
Identity
Primary key
Unique
Foreign table
Foreign field
Relationship type
Relationship name
Cardinality
Unit
Min value
Excl min
Max value
Excl max
Multiple of
Divisible by
Pattern
Enum
Faker function
Sample 2181.5625
Remarks
2.1.2.22.2.20 Column TaxAmt
2.1.2.22.2.20.1 TaxAmt properties
PropertyValue
Business Name TaxAmt
Technical name
Activated true
Id
Type numeric
Subtype money
Default
Default constraint name
Comments
Not null true
Masked with function
Identity
Primary key
Unique
Foreign table
Foreign field
Relationship type
Relationship name
Cardinality
Unit
Min value
Excl min
Max value
Excl max
Multiple of
Divisible by
Pattern
Enum
Faker function
Sample 174.525
Remarks
2.1.2.22.2.21 Column Freight
2.1.2.22.2.21.1 Freight properties
PropertyValue
Business Name Freight
Technical name
Activated true
Id
Type numeric
Subtype money
Default
Default constraint name
Comments
Not null true
Masked with function
Identity
Primary key
Unique
Foreign table
Foreign field
Relationship type
Relationship name
Cardinality
Unit
Min value
Excl min
Max value
Excl max
Multiple of
Divisible by
Pattern
Enum
Faker function
Sample 54.5391
Remarks
2.1.2.22.2.22 Column CarrierTrackingNumber
2.1.2.22.2.22.1 CarrierTrackingNumber properties
PropertyValue
Business Name CarrierTrackingNumber
Technical name
Activated true
Id
Type char
Subtype nvarchar
Length 25
Default
Collate
Default constraint name
Comments
Not null
Masked with function
Primary key
Unique
Foreign table
Foreign field
Relationship type
Relationship name
Cardinality
Min length
Pattern
Format
Enum
Faker function
Sample
Remarks
2.1.2.22.2.23 Column CustomerPONumber
2.1.2.22.2.23.1 CustomerPONumber properties
PropertyValue
Business Name CustomerPONumber
Technical name
Activated true
Id
Type char
Subtype nvarchar
Length 25
Default
Collate
Default constraint name
Comments
Not null
Masked with function
Primary key
Unique
Foreign table
Foreign field
Relationship type
Relationship name
Cardinality
Min length
Pattern
Format
Enum
Faker function
Sample
Remarks
2.1.2.22.3 FactInternetSales Composite keys
PropertyValue
Primary key
[1] Constraint name
Key
Unique key
[1] Constraint name
Key
Alternate key
2.1.2.22.4 FactInternetSales JSON Schema
{
    "$schema": "http://json-schema.org/draft-04/schema#",
    "type": "object",
    "title": "FactInternetSales",
    "additionalProperties": false,
    "properties": {
        "ProductKey": {
            "type": "number"
        },
        "OrderDateKey": {
            "type": "number"
        },
        "DueDateKey": {
            "type": "number"
        },
        "ShipDateKey": {
            "type": "number"
        },
        "CustomerKey": {
            "type": "number"
        },
        "PromotionKey": {
            "type": "number"
        },
        "CurrencyKey": {
            "type": "number"
        },
        "SalesTerritoryKey": {
            "type": "number"
        },
        "SalesOrderNumber": {
            "type": "string"
        },
        "SalesOrderLineNumber": {
            "type": "number"
        },
        "RevisionNumber": {
            "type": "number"
        },
        "OrderQuantity": {
            "type": "number"
        },
        "UnitPrice": {
            "type": "number"
        },
        "ExtendedAmount": {
            "type": "number"
        },
        "UnitPriceDiscountPct": {
            "type": "number"
        },
        "DiscountAmount": {
            "type": "number"
        },
        "ProductStandardCost": {
            "type": "number"
        },
        "TotalProductCost": {
            "type": "number"
        },
        "SalesAmount": {
            "type": "number"
        },
        "TaxAmt": {
            "type": "number"
        },
        "Freight": {
            "type": "number"
        },
        "CarrierTrackingNumber": {
            "type": "string"
        },
        "CustomerPONumber": {
            "type": "string"
        }
    },
    "required": [
        "ProductKey",
        "OrderDateKey",
        "DueDateKey",
        "ShipDateKey",
        "CustomerKey",
        "PromotionKey",
        "CurrencyKey",
        "SalesTerritoryKey",
        "SalesOrderNumber",
        "SalesOrderLineNumber",
        "RevisionNumber",
        "OrderQuantity",
        "UnitPrice",
        "ExtendedAmount",
        "UnitPriceDiscountPct",
        "DiscountAmount",
        "ProductStandardCost",
        "TotalProductCost",
        "SalesAmount",
        "TaxAmt",
        "Freight"
    ]
}
2.1.2.22.5 FactInternetSales JSON data
{
    "ProductKey": 373,
    "OrderDateKey": 20020803,
    "DueDateKey": 20020815,
    "ShipDateKey": 20020810,
    "CustomerKey": 13378,
    "PromotionKey": 1,
    "CurrencyKey": 100,
    "SalesTerritoryKey": 4,
    "SalesOrderNumber": "SO47086",
    "SalesOrderLineNumber": 1,
    "RevisionNumber": 1,
    "OrderQuantity": 1,
    "UnitPrice": 2181.5625,
    "ExtendedAmount": 2181.5625,
    "UnitPriceDiscountPct": 0,
    "DiscountAmount": 0,
    "ProductStandardCost": 1320.6838,
    "TotalProductCost": 1320.6838,
    "SalesAmount": 2181.5625,
    "TaxAmt": 174.525,
    "Freight": 54.5391,
    "CarrierTrackingNumber": "Lorem",
    "CustomerPONumber": "Lorem"
}
2.1.2.22.6 FactInternetSales Target Script
IF NOT EXISTS (SELECT * FROM sys.databases WHERE name = N'[hacksqlpool]')
begin
	CREATE DATABASE [hacksqlpool];
	USE [hacksqlpool];
end;

CREATE SCHEMA [dbo];

CREATE TABLE [dbo].[FactInternetSales] (
	[ProductKey] INT PRIMARY KEY NONCLUSTERED NOT ENFORCED NOT NULL,
	[OrderDateKey] INT NOT NULL,
	[DueDateKey] INT NOT NULL,
	[ShipDateKey] INT NOT NULL,
	[CustomerKey] INT NOT NULL,
	[PromotionKey] INT NOT NULL,
	[CurrencyKey] INT NOT NULL,
	[SalesTerritoryKey] INT NOT NULL,
	[SalesOrderNumber] NVARCHAR(20) NOT NULL,
	[SalesOrderLineNumber] TINYINT NOT NULL,
	[RevisionNumber] TINYINT NOT NULL,
	[OrderQuantity] SMALLINT NOT NULL,
	[UnitPrice] MONEY NOT NULL,
	[ExtendedAmount] MONEY NOT NULL,
	[UnitPriceDiscountPct] FLOAT NOT NULL,
	[DiscountAmount] FLOAT NOT NULL,
	[ProductStandardCost] MONEY NOT NULL,
	[TotalProductCost] MONEY NOT NULL,
	[SalesAmount] MONEY NOT NULL,
	[TaxAmt] MONEY NOT NULL,
	[Freight] MONEY NOT NULL,
	[CarrierTrackingNumber] NVARCHAR(25),
	[CustomerPONumber] NVARCHAR(25)
)WITH (
	CLUSTERED COLUMNSTORE INDEX,
	DISTRIBUTION = ROUND_ROBIN
);
2.1.2.23 Table FactInternetSalesReason
2.1.2.23.1 FactInternetSalesReason Properties
PropertyValue
TableFactInternetSalesReason
Technical name
Activatedtrue
Id
Schemadbo
Additional propertiesfalse
$ref
Comments
If not exist
Distributionround_robin
Persistenceregular
SQL pool indexingclustered columnstore index
Partition
CTAS query
Range for values
Boundary value
Remarks
2.1.2.23.2 FactInternetSalesReason Column
ColumnTypeReqKeyDescriptionComments
SalesOrderNumbernvarchar(20)truepk, dk
SalesOrderLineNumbertinyinttrue
SalesReasonKeyinttruefk
2.1.2.23.2.1 Column SalesOrderNumber
2.1.2.23.2.1.1 SalesOrderNumber properties
PropertyValue
Business Name SalesOrderNumber
Technical name
Activated true
Id
Type char
Subtype nvarchar
Length 20
Default
Collate
Default constraint name
Comments
Not null true
Masked with function
Primary key true
Foreign table
Foreign field
Relationship type
Relationship name
Cardinality
Min length
Pattern
Format
Enum
Faker function
Sample SO44461
Remarks
2.1.2.23.2.2 Column SalesOrderLineNumber
2.1.2.23.2.2.1 SalesOrderLineNumber properties
PropertyValue
Business Name SalesOrderLineNumber
Technical name
Activated true
Id
Type numeric
Subtype tinyint
Default
Default constraint name
Comments
Not null true
Masked with function
Identity
Primary key
Unique
Foreign table
Foreign field
Relationship type
Relationship name
Cardinality
Unit
Min value
Excl min
Max value
Excl max
Multiple of
Divisible by
Pattern
Enum
Faker function
Sample 1
Remarks
2.1.2.23.2.3 Column SalesReasonKey
2.1.2.23.2.3.1 SalesReasonKey properties
PropertyValue
Business Name SalesReasonKey
Technical name
Activated true
Id
Type numeric
Subtype int
Default
Default constraint name
Comments
Not null true
Masked with function
Identity
Primary key
Unique
Foreign table DimSalesReason
Foreign field SalesReasonKey
Relationship type Foreign Key
Relationship name fk FactInternetSalesReason.SalesReasonKey to DimSalesReason.SalesReasonKey
Cardinality 1..n
Unit
Min value
Excl min
Max value
Excl max
Multiple of
Divisible by
Pattern
Enum
Faker function
Sample 5
Remarks
2.1.2.23.3 FactInternetSalesReason Composite keys
PropertyValue
Primary key
[1] Constraint name
Key
Unique key
[1] Constraint name
Key
Alternate key
2.1.2.23.4 FactInternetSalesReason JSON Schema
{
    "$schema": "http://json-schema.org/draft-04/schema#",
    "type": "object",
    "title": "FactInternetSalesReason",
    "additionalProperties": false,
    "properties": {
        "SalesOrderNumber": {
            "type": "string"
        },
        "SalesOrderLineNumber": {
            "type": "number"
        },
        "SalesReasonKey": {
            "type": "number"
        }
    },
    "required": [
        "SalesOrderNumber",
        "SalesOrderLineNumber",
        "SalesReasonKey"
    ]
}
2.1.2.23.5 FactInternetSalesReason JSON data
{
    "SalesOrderNumber": "SO44461",
    "SalesOrderLineNumber": 1,
    "SalesReasonKey": 5
}
2.1.2.23.6 FactInternetSalesReason Target Script
IF NOT EXISTS (SELECT * FROM sys.databases WHERE name = N'[hacksqlpool]')
begin
	CREATE DATABASE [hacksqlpool];
	USE [hacksqlpool];
end;

CREATE SCHEMA [dbo];

CREATE TABLE [dbo].[FactInternetSalesReason] (
	[SalesOrderNumber] NVARCHAR(20) PRIMARY KEY NONCLUSTERED NOT ENFORCED NOT NULL,
	[SalesOrderLineNumber] TINYINT NOT NULL,
	[SalesReasonKey] INT NOT NULL
)WITH (
	CLUSTERED COLUMNSTORE INDEX,
	DISTRIBUTION = ROUND_ROBIN
);
2.1.2.24 Table FactResellerSales
2.1.2.24.1 FactResellerSales Properties
PropertyValue
TableFactResellerSales
Technical name
Activatedtrue
Id
Schemadbo
Additional propertiesfalse
$ref
Comments
If not exist
Distributionround_robin
Persistenceregular
SQL pool indexingclustered columnstore index
Partition
CTAS query
Range for values
Boundary value
Remarks
2.1.2.24.2 FactResellerSales Column
ColumnTypeReqKeyDescriptionComments
ProductKeyinttruepk
OrderDateKeyinttrue
DueDateKeyinttruefk
ShipDateKeyinttruefk
ResellerKeyinttruefk
EmployeeKeyinttruefk
PromotionKeyinttruefk
CurrencyKeyinttruefk
SalesTerritoryKeyinttruefk
SalesOrderNumbernvarchar(20)truefk
SalesOrderLineNumbertinyinttrue
RevisionNumbertinyintfalse
OrderQuantitysmallintfalse
UnitPricemoneyfalse
ExtendedAmountmoneyfalse
UnitPriceDiscountPctfloatfalse
DiscountAmountfloatfalse
ProductStandardCostmoneyfalse
TotalProductCostmoneyfalse
SalesAmountmoneyfalse
TaxAmtmoneyfalse
Freightmoneyfalse
CarrierTrackingNumbernvarchar(25)false
CustomerPONumbernvarchar(25)false
2.1.2.24.2.1 Column ProductKey
2.1.2.24.2.1.1 ProductKey properties
PropertyValue
Business Name ProductKey
Technical name
Activated true
Id
Type numeric
Subtype int
Default
Default constraint name
Comments
Not null true
Masked with function
Identity
Primary key true
Foreign table
Foreign field
Relationship type
Relationship name
Cardinality
Unit
Min value
Excl min
Max value
Excl max
Multiple of
Divisible by
Pattern
Enum
Faker function
Sample 230
Remarks
2.1.2.24.2.2 Column OrderDateKey
2.1.2.24.2.2.1 OrderDateKey properties
PropertyValue
Business Name OrderDateKey
Technical name
Activated true
Id
Type numeric
Subtype int
Default
Default constraint name
Comments
Not null true
Masked with function
Identity
Primary key
Unique
Foreign table
Foreign field
Relationship type
Relationship name
Cardinality
Unit
Min value
Excl min
Max value
Excl max
Multiple of
Divisible by
Pattern
Enum
Faker function
Sample 20020701
Remarks
2.1.2.24.2.3 Column DueDateKey
2.1.2.24.2.3.1 DueDateKey properties
PropertyValue
Business Name DueDateKey
Technical name
Activated true
Id
Type numeric
Subtype int
Default
Default constraint name
Comments
Not null true
Masked with function
Identity
Primary key
Unique
Foreign table DimDate
Foreign field DateKey
Relationship type Foreign Key
Relationship name fk FactResellerSales.DueDateKey to DimDate.DateKey
Cardinality 1..n
Unit
Min value
Excl min
Max value
Excl max
Multiple of
Divisible by
Pattern
Enum
Faker function
Sample 20020713
Remarks
2.1.2.24.2.4 Column ShipDateKey
2.1.2.24.2.4.1 ShipDateKey properties
PropertyValue
Business Name ShipDateKey
Technical name
Activated true
Id
Type numeric
Subtype int
Default
Default constraint name
Comments
Not null true
Masked with function
Identity
Primary key
Unique
Foreign table DimDate
Foreign field DateKey
Relationship type Foreign Key
Relationship name fk FactResellerSales.ShipDateKey to DimDate.DateKey
Cardinality 1..n
Unit
Min value
Excl min
Max value
Excl max
Multiple of
Divisible by
Pattern
Enum
Faker function
Sample 20020708
Remarks
2.1.2.24.2.5 Column ResellerKey
2.1.2.24.2.5.1 ResellerKey properties
PropertyValue
Business Name ResellerKey
Technical name
Activated true
Id
Type numeric
Subtype int
Default
Default constraint name
Comments
Not null true
Masked with function
Identity
Primary key
Unique
Foreign table DimReseller
Foreign field ResellerKey
Relationship type Foreign Key
Relationship name fk FactResellerSales.ResellerKey to DimReseller.ResellerKey
Cardinality 1..n
Unit
Min value
Excl min
Max value
Excl max
Multiple of
Divisible by
Pattern
Enum
Faker function
Sample 618
Remarks
2.1.2.24.2.6 Column EmployeeKey
2.1.2.24.2.6.1 EmployeeKey properties
PropertyValue
Business Name EmployeeKey
Technical name
Activated true
Id
Type numeric
Subtype int
Default
Default constraint name
Comments
Not null true
Masked with function
Identity
Primary key
Unique
Foreign table DimEmployee
Foreign field EmployeeKey
Relationship type Foreign Key
Relationship name fk FactResellerSales.EmployeeKey to DimEmployee.EmployeeKey
Cardinality 1..n
Unit
Min value
Excl min
Max value
Excl max
Multiple of
Divisible by
Pattern
Enum
Faker function
Sample 281
Remarks
2.1.2.24.2.7 Column PromotionKey
2.1.2.24.2.7.1 PromotionKey properties
PropertyValue
Business Name PromotionKey
Technical name
Activated true
Id
Type numeric
Subtype int
Default
Default constraint name
Comments
Not null true
Masked with function
Identity
Primary key
Unique
Foreign table DimPromotion
Foreign field PromotionKey
Relationship type Foreign Key
Relationship name fk FactResellerSales.PromotionKey to DimPromotion.PromotionKey
Cardinality 1..n
Unit
Min value
Excl min
Max value
Excl max
Multiple of
Divisible by
Pattern
Enum
Faker function
Sample 1
Remarks
2.1.2.24.2.8 Column CurrencyKey
2.1.2.24.2.8.1 CurrencyKey properties
PropertyValue
Business Name CurrencyKey
Technical name
Activated true
Id
Type numeric
Subtype int
Default
Default constraint name
Comments
Not null true
Masked with function
Identity
Primary key
Unique
Foreign table DimCurrency
Foreign field CurrencyKey
Relationship type Foreign Key
Relationship name fk FactResellerSales.CurrencyKey to DimCurrency.CurrencyKey
Cardinality 1..n
Unit
Min value
Excl min
Max value
Excl max
Multiple of
Divisible by
Pattern
Enum
Faker function
Sample 100
Remarks
2.1.2.24.2.9 Column SalesTerritoryKey
2.1.2.24.2.9.1 SalesTerritoryKey properties
PropertyValue
Business Name SalesTerritoryKey
Technical name
Activated true
Id
Type numeric
Subtype int
Default
Default constraint name
Comments
Not null true
Masked with function
Identity
Primary key
Unique
Foreign table DimSalesTerritory
Foreign field SalesTerritoryKey
Relationship type Foreign Key
Relationship name fk FactResellerSales.SalesTerritoryKey to DimSalesTerritory.SalesTerritoryKey
Cardinality 1..n
Unit
Min value
Excl min
Max value
Excl max
Multiple of
Divisible by
Pattern
Enum
Faker function
Sample 2
Remarks
2.1.2.24.2.10 Column SalesOrderNumber
2.1.2.24.2.10.1 SalesOrderNumber properties
PropertyValue
Business Name SalesOrderNumber
Technical name
Activated true
Id
Type char
Subtype nvarchar
Length 20
Default
Collate
Default constraint name
Comments
Not null true
Masked with function
Primary key
Unique
Foreign table FactInternetSalesReason
Foreign field SalesOrderNumber
Relationship type Foreign Key
Relationship name fk FactResellerSales.SalesOrderNumber to FactInternetSalesReason.SalesOrderNumber
Cardinality 1..n
Min length
Pattern
Format
Enum
Faker function
Sample SO46620
Remarks
2.1.2.24.2.11 Column SalesOrderLineNumber
2.1.2.24.2.11.1 SalesOrderLineNumber properties
PropertyValue
Business Name SalesOrderLineNumber
Technical name
Activated true
Id
Type numeric
Subtype tinyint
Default
Default constraint name
Comments
Not null true
Masked with function
Identity
Primary key
Unique
Foreign table
Foreign field
Relationship type
Relationship name
Cardinality
Unit
Min value
Excl min
Max value
Excl max
Multiple of
Divisible by
Pattern
Enum
Faker function
Sample 49
Remarks
2.1.2.24.2.12 Column RevisionNumber
2.1.2.24.2.12.1 RevisionNumber properties
PropertyValue
Business Name RevisionNumber
Technical name
Activated true
Id
Type numeric
Subtype tinyint
Default
Default constraint name
Comments
Not null
Masked with function
Identity
Primary key
Unique
Foreign table
Foreign field
Relationship type
Relationship name
Cardinality
Unit
Min value
Excl min
Max value
Excl max
Multiple of
Divisible by
Pattern
Enum
Faker function
Sample 1
Remarks
2.1.2.24.2.13 Column OrderQuantity
2.1.2.24.2.13.1 OrderQuantity properties
PropertyValue
Business Name OrderQuantity
Technical name
Activated true
Id
Type numeric
Subtype smallint
Default
Default constraint name
Comments
Not null
Masked with function
Identity
Primary key
Unique
Foreign table
Foreign field
Relationship type
Relationship name
Cardinality
Unit
Min value
Excl min
Max value
Excl max
Multiple of
Divisible by
Pattern
Enum
Faker function
Sample 4
Remarks
2.1.2.24.2.14 Column UnitPrice
2.1.2.24.2.14.1 UnitPrice properties
PropertyValue
Business Name UnitPrice
Technical name
Activated true
Id
Type numeric
Subtype money
Default
Default constraint name
Comments
Not null
Masked with function
Identity
Primary key
Unique
Foreign table
Foreign field
Relationship type
Relationship name
Cardinality
Unit
Min value
Excl min
Max value
Excl max
Multiple of
Divisible by
Pattern
Enum
Faker function
Sample 28.8404
Remarks
2.1.2.24.2.15 Column ExtendedAmount
2.1.2.24.2.15.1 ExtendedAmount properties
PropertyValue
Business Name ExtendedAmount
Technical name
Activated true
Id
Type numeric
Subtype money
Default
Default constraint name
Comments
Not null
Masked with function
Identity
Primary key
Unique
Foreign table
Foreign field
Relationship type
Relationship name
Cardinality
Unit
Min value
Excl min
Max value
Excl max
Multiple of
Divisible by
Pattern
Enum
Faker function
Sample 115.3616
Remarks
2.1.2.24.2.16 Column UnitPriceDiscountPct
2.1.2.24.2.16.1 UnitPriceDiscountPct properties
PropertyValue
Business Name UnitPriceDiscountPct
Technical name
Activated true
Id
Type numeric
Subtype float
Default
Default constraint name
Comments
Not null
Masked with function
Identity
Primary key
Unique
Foreign table
Foreign field
Relationship type
Relationship name
Cardinality
Unit
Min value
Excl min
Max value
Excl max
Multiple of
Divisible by
Pattern
Enum
Faker function
Sample 0
Remarks
2.1.2.24.2.17 Column DiscountAmount
2.1.2.24.2.17.1 DiscountAmount properties
PropertyValue
Business Name DiscountAmount
Technical name
Activated true
Id
Type numeric
Subtype float
Default
Default constraint name
Comments
Not null
Masked with function
Identity
Primary key
Unique
Foreign table
Foreign field
Relationship type
Relationship name
Cardinality
Unit
Min value
Excl min
Max value
Excl max
Multiple of
Divisible by
Pattern
Enum
Faker function
Sample 0
Remarks
2.1.2.24.2.18 Column ProductStandardCost
2.1.2.24.2.18.1 ProductStandardCost properties
PropertyValue
Business Name ProductStandardCost
Technical name
Activated true
Id
Type numeric
Subtype money
Default
Default constraint name
Comments
Not null
Masked with function
Identity
Primary key
Unique
Foreign table
Foreign field
Relationship type
Relationship name
Cardinality
Unit
Min value
Excl min
Max value
Excl max
Multiple of
Divisible by
Pattern
Enum
Faker function
Sample 29.0807
Remarks
2.1.2.24.2.19 Column TotalProductCost
2.1.2.24.2.19.1 TotalProductCost properties
PropertyValue
Business Name TotalProductCost
Technical name
Activated true
Id
Type numeric
Subtype money
Default
Default constraint name
Comments
Not null
Masked with function
Identity
Primary key
Unique
Foreign table
Foreign field
Relationship type
Relationship name
Cardinality
Unit
Min value
Excl min
Max value
Excl max
Multiple of
Divisible by
Pattern
Enum
Faker function
Sample 116.3228
Remarks
2.1.2.24.2.20 Column SalesAmount
2.1.2.24.2.20.1 SalesAmount properties
PropertyValue
Business Name SalesAmount
Technical name
Activated true
Id
Type numeric
Subtype money
Default
Default constraint name
Comments
Not null
Masked with function
Identity
Primary key
Unique
Foreign table
Foreign field
Relationship type
Relationship name
Cardinality
Unit
Min value
Excl min
Max value
Excl max
Multiple of
Divisible by
Pattern
Enum
Faker function
Sample 115.3616
Remarks
2.1.2.24.2.21 Column TaxAmt
2.1.2.24.2.21.1 TaxAmt properties
PropertyValue
Business Name TaxAmt
Technical name
Activated true
Id
Type numeric
Subtype money
Default
Default constraint name
Comments
Not null
Masked with function
Identity
Primary key
Unique
Foreign table
Foreign field
Relationship type
Relationship name
Cardinality
Unit
Min value
Excl min
Max value
Excl max
Multiple of
Divisible by
Pattern
Enum
Faker function
Sample 9.2289
Remarks
2.1.2.24.2.22 Column Freight
2.1.2.24.2.22.1 Freight properties
PropertyValue
Business Name Freight
Technical name
Activated true
Id
Type numeric
Subtype money
Default
Default constraint name
Comments
Not null
Masked with function
Identity
Primary key
Unique
Foreign table
Foreign field
Relationship type
Relationship name
Cardinality
Unit
Min value
Excl min
Max value
Excl max
Multiple of
Divisible by
Pattern
Enum
Faker function
Sample 2.884
Remarks
2.1.2.24.2.23 Column CarrierTrackingNumber
2.1.2.24.2.23.1 CarrierTrackingNumber properties
PropertyValue
Business Name CarrierTrackingNumber
Technical name
Activated true
Id
Type char
Subtype nvarchar
Length 25
Default
Collate
Default constraint name
Comments
Not null
Masked with function
Primary key
Unique
Foreign table
Foreign field
Relationship type
Relationship name
Cardinality
Min length
Pattern
Format
Enum
Faker function
Sample FEE3-4994-8E
Remarks
2.1.2.24.2.24 Column CustomerPONumber
2.1.2.24.2.24.1 CustomerPONumber properties
PropertyValue
Business Name CustomerPONumber
Technical name
Activated true
Id
Type char
Subtype nvarchar
Length 25
Default
Collate
Default constraint name
Comments
Not null
Masked with function
Primary key
Unique
Foreign table
Foreign field
Relationship type
Relationship name
Cardinality
Min length
Pattern
Format
Enum
Faker function
Sample PO13775143788
Remarks
2.1.2.24.3 FactResellerSales Composite keys
PropertyValue
Primary key
[1] Constraint name
Key
Unique key
[1] Constraint name
Key
Alternate key
2.1.2.24.4 FactResellerSales JSON Schema
{
    "$schema": "http://json-schema.org/draft-04/schema#",
    "type": "object",
    "title": "FactResellerSales",
    "additionalProperties": false,
    "properties": {
        "ProductKey": {
            "type": "number"
        },
        "OrderDateKey": {
            "type": "number"
        },
        "DueDateKey": {
            "type": "number"
        },
        "ShipDateKey": {
            "type": "number"
        },
        "ResellerKey": {
            "type": "number"
        },
        "EmployeeKey": {
            "type": "number"
        },
        "PromotionKey": {
            "type": "number"
        },
        "CurrencyKey": {
            "type": "number"
        },
        "SalesTerritoryKey": {
            "type": "number"
        },
        "SalesOrderNumber": {
            "type": "string"
        },
        "SalesOrderLineNumber": {
            "type": "number"
        },
        "RevisionNumber": {
            "type": "number"
        },
        "OrderQuantity": {
            "type": "number"
        },
        "UnitPrice": {
            "type": "number"
        },
        "ExtendedAmount": {
            "type": "number"
        },
        "UnitPriceDiscountPct": {
            "type": "number"
        },
        "DiscountAmount": {
            "type": "number"
        },
        "ProductStandardCost": {
            "type": "number"
        },
        "TotalProductCost": {
            "type": "number"
        },
        "SalesAmount": {
            "type": "number"
        },
        "TaxAmt": {
            "type": "number"
        },
        "Freight": {
            "type": "number"
        },
        "CarrierTrackingNumber": {
            "type": "string"
        },
        "CustomerPONumber": {
            "type": "string"
        }
    },
    "required": [
        "ProductKey",
        "OrderDateKey",
        "DueDateKey",
        "ShipDateKey",
        "ResellerKey",
        "EmployeeKey",
        "PromotionKey",
        "CurrencyKey",
        "SalesTerritoryKey",
        "SalesOrderNumber",
        "SalesOrderLineNumber"
    ]
}
2.1.2.24.5 FactResellerSales JSON data
{
    "ProductKey": 230,
    "OrderDateKey": 20020701,
    "DueDateKey": 20020713,
    "ShipDateKey": 20020708,
    "ResellerKey": 618,
    "EmployeeKey": 281,
    "PromotionKey": 1,
    "CurrencyKey": 100,
    "SalesTerritoryKey": 2,
    "SalesOrderNumber": "SO46620",
    "SalesOrderLineNumber": 49,
    "RevisionNumber": 1,
    "OrderQuantity": 4,
    "UnitPrice": 28.8404,
    "ExtendedAmount": 115.3616,
    "UnitPriceDiscountPct": 0,
    "DiscountAmount": 0,
    "ProductStandardCost": 29.0807,
    "TotalProductCost": 116.3228,
    "SalesAmount": 115.3616,
    "TaxAmt": 9.2289,
    "Freight": 2.884,
    "CarrierTrackingNumber": "FEE3-4994-8E",
    "CustomerPONumber": "PO13775143788"
}
2.1.2.24.6 FactResellerSales Target Script
IF NOT EXISTS (SELECT * FROM sys.databases WHERE name = N'[hacksqlpool]')
begin
	CREATE DATABASE [hacksqlpool];
	USE [hacksqlpool];
end;

CREATE SCHEMA [dbo];

CREATE TABLE [dbo].[FactResellerSales] (
	[ProductKey] INT PRIMARY KEY NONCLUSTERED NOT ENFORCED NOT NULL,
	[OrderDateKey] INT NOT NULL,
	[DueDateKey] INT NOT NULL,
	[ShipDateKey] INT NOT NULL,
	[ResellerKey] INT NOT NULL,
	[EmployeeKey] INT NOT NULL,
	[PromotionKey] INT NOT NULL,
	[CurrencyKey] INT NOT NULL,
	[SalesTerritoryKey] INT NOT NULL,
	[SalesOrderNumber] NVARCHAR(20) NOT NULL,
	[SalesOrderLineNumber] TINYINT NOT NULL,
	[RevisionNumber] TINYINT,
	[OrderQuantity] SMALLINT,
	[UnitPrice] MONEY,
	[ExtendedAmount] MONEY,
	[UnitPriceDiscountPct] FLOAT,
	[DiscountAmount] FLOAT,
	[ProductStandardCost] MONEY,
	[TotalProductCost] MONEY,
	[SalesAmount] MONEY,
	[TaxAmt] MONEY,
	[Freight] MONEY,
	[CarrierTrackingNumber] NVARCHAR(25),
	[CustomerPONumber] NVARCHAR(25)
)WITH (
	CLUSTERED COLUMNSTORE INDEX,
	DISTRIBUTION = ROUND_ROBIN
);
2.1.2.25 Table FactSalesQuota
2.1.2.25.1 FactSalesQuota Properties
PropertyValue
TableFactSalesQuota
Technical name
Activatedtrue
Id
Schemadbo
Additional propertiesfalse
$ref
Comments
If not exist
Distributionround_robin
Persistenceregular
SQL pool indexingclustered columnstore index
Partition
CTAS query
Range for values
Boundary value
Remarks
2.1.2.25.2 FactSalesQuota Column
ColumnTypeReqKeyDescriptionComments
SalesQuotaKeyinttruepk
EmployeeKeyinttruefk
DateKeyinttruefk
CalendarYearsmallinttrue
CalendarQuartertinyinttrue
SalesAmountQuotamoneytrue
2.1.2.25.2.1 Column SalesQuotaKey
2.1.2.25.2.1.1 SalesQuotaKey properties
PropertyValue
Business Name SalesQuotaKey
Technical name
Activated true
Id
Type numeric
Subtype int
Default
Default constraint name
Comments
Not null true
Masked with function
Identity
Primary key true
Foreign table
Foreign field
Relationship type
Relationship name
Cardinality
Unit
Min value
Excl min
Max value
Excl max
Multiple of
Divisible by
Pattern
Enum
Faker function
Sample 135
Remarks
2.1.2.25.2.2 Column EmployeeKey
2.1.2.25.2.2.1 EmployeeKey properties
PropertyValue
Business Name EmployeeKey
Technical name
Activated true
Id
Type numeric
Subtype int
Default
Default constraint name
Comments
Not null true
Masked with function
Identity
Primary key
Unique
Foreign table DimEmployee
Foreign field EmployeeKey
Relationship type Foreign Key
Relationship name fk FactSalesQuota.EmployeeKey to DimEmployee.EmployeeKey
Cardinality 1..n
Unit
Min value
Excl min
Max value
Excl max
Multiple of
Divisible by
Pattern
Enum
Faker function
Sample 285
Remarks
2.1.2.25.2.3 Column DateKey
2.1.2.25.2.3.1 DateKey properties
PropertyValue
Business Name DateKey
Technical name
Activated true
Id
Type numeric
Subtype int
Default
Default constraint name
Comments
Not null true
Masked with function
Identity
Primary key
Unique
Foreign table DimDate
Foreign field DateKey
Relationship type Foreign Key
Relationship name fk FactSalesQuota.DateKey to DimDate.DateKey
Cardinality 1..n
Unit
Min value
Excl min
Max value
Excl max
Multiple of
Divisible by
Pattern
Enum
Faker function
Sample 20040101
Remarks
2.1.2.25.2.4 Column CalendarYear
2.1.2.25.2.4.1 CalendarYear properties
PropertyValue
Business Name CalendarYear
Technical name
Activated true
Id
Type numeric
Subtype smallint
Default
Default constraint name
Comments
Not null true
Masked with function
Identity
Primary key
Unique
Foreign table
Foreign field
Relationship type
Relationship name
Cardinality
Unit
Min value
Excl min
Max value
Excl max
Multiple of
Divisible by
Pattern
Enum
Faker function
Sample 2004
Remarks
2.1.2.25.2.5 Column CalendarQuarter
2.1.2.25.2.5.1 CalendarQuarter properties
PropertyValue
Business Name CalendarQuarter
Technical name
Activated true
Id
Type numeric
Subtype tinyint
Default
Default constraint name
Comments
Not null true
Masked with function
Identity
Primary key
Unique
Foreign table
Foreign field
Relationship type
Relationship name
Cardinality
Unit
Min value
Excl min
Max value
Excl max
Multiple of
Divisible by
Pattern
Enum
Faker function
Sample 1
Remarks
2.1.2.25.2.6 Column SalesAmountQuota
2.1.2.25.2.6.1 SalesAmountQuota properties
PropertyValue
Business Name SalesAmountQuota
Technical name
Activated true
Id
Type numeric
Subtype money
Default
Default constraint name
Comments
Not null true
Masked with function
Identity
Primary key
Unique
Foreign table
Foreign field
Relationship type
Relationship name
Cardinality
Unit
Min value
Excl min
Max value
Excl max
Multiple of
Divisible by
Pattern
Enum
Faker function
Sample 538000
Remarks
2.1.2.25.3 FactSalesQuota Composite keys
PropertyValue
Primary key
[1] Constraint name
Key
Unique key
[1] Constraint name
Key
Alternate key
2.1.2.25.4 FactSalesQuota JSON Schema
{
    "$schema": "http://json-schema.org/draft-04/schema#",
    "type": "object",
    "title": "FactSalesQuota",
    "additionalProperties": false,
    "properties": {
        "SalesQuotaKey": {
            "type": "number"
        },
        "EmployeeKey": {
            "type": "number"
        },
        "DateKey": {
            "type": "number"
        },
        "CalendarYear": {
            "type": "number"
        },
        "CalendarQuarter": {
            "type": "number"
        },
        "SalesAmountQuota": {
            "type": "number"
        }
    },
    "required": [
        "SalesQuotaKey",
        "EmployeeKey",
        "DateKey",
        "CalendarYear",
        "CalendarQuarter",
        "SalesAmountQuota"
    ]
}
2.1.2.25.5 FactSalesQuota JSON data
{
    "SalesQuotaKey": 135,
    "EmployeeKey": 285,
    "DateKey": 20040101,
    "CalendarYear": 2004,
    "CalendarQuarter": 1,
    "SalesAmountQuota": 538000
}
2.1.2.25.6 FactSalesQuota Target Script
IF NOT EXISTS (SELECT * FROM sys.databases WHERE name = N'[hacksqlpool]')
begin
	CREATE DATABASE [hacksqlpool];
	USE [hacksqlpool];
end;

CREATE SCHEMA [dbo];

CREATE TABLE [dbo].[FactSalesQuota] (
	[SalesQuotaKey] INT PRIMARY KEY NONCLUSTERED NOT ENFORCED NOT NULL,
	[EmployeeKey] INT NOT NULL,
	[DateKey] INT NOT NULL,
	[CalendarYear] SMALLINT NOT NULL,
	[CalendarQuarter] TINYINT NOT NULL,
	[SalesAmountQuota] MONEY NOT NULL
)WITH (
	CLUSTERED COLUMNSTORE INDEX,
	DISTRIBUTION = ROUND_ROBIN
);
2.1.2.26 Table FactSurveyResponse
2.1.2.26.1 FactSurveyResponse Properties
PropertyValue
TableFactSurveyResponse
Technical name
Activatedtrue
Id
Schemadbo
Additional propertiesfalse
$ref
Comments
If not exist
Distributionround_robin
Persistenceregular
SQL pool indexingclustered columnstore index
Partition
CTAS query
Range for values
Boundary value
Remarks
2.1.2.26.2 FactSurveyResponse Column
ColumnTypeReqKeyDescriptionComments
SurveyResponseKeyinttruepk
DateKeyinttruefk
CustomerKeyinttruefk
ProductCategoryKeyinttruefk
EnglishProductCategoryNamenvarchar(50)true
ProductSubcategoryKeyinttruefk
EnglishProductSubcategoryNamenvarchar(50)true
2.1.2.26.2.1 Column SurveyResponseKey
2.1.2.26.2.1.1 SurveyResponseKey properties
PropertyValue
Business Name SurveyResponseKey
Technical name
Activated true
Id
Type numeric
Subtype int
Default
Default constraint name
Comments
Not null true
Masked with function
Identity
Primary key true
Foreign table
Foreign field
Relationship type
Relationship name
Cardinality
Unit
Min value
Excl min
Max value
Excl max
Multiple of
Divisible by
Pattern
Enum
Faker function
Sample 527
Remarks
2.1.2.26.2.2 Column DateKey
2.1.2.26.2.2.1 DateKey properties
PropertyValue
Business Name DateKey
Technical name
Activated true
Id
Type numeric
Subtype int
Default
Default constraint name
Comments
Not null true
Masked with function
Identity
Primary key
Unique
Foreign table DimDate
Foreign field DateKey
Relationship type Foreign Key
Relationship name fk FactSurveyResponse.DateKey to DimDate.DateKey
Cardinality 1..n
Unit
Min value
Excl min
Max value
Excl max
Multiple of
Divisible by
Pattern
Enum
Faker function
Sample 20020906
Remarks
2.1.2.26.2.3 Column CustomerKey
2.1.2.26.2.3.1 CustomerKey properties
PropertyValue
Business Name CustomerKey
Technical name
Activated true
Id
Type numeric
Subtype int
Default
Default constraint name
Comments
Not null true
Masked with function
Identity
Primary key
Unique
Foreign table DimCustomer
Foreign field CustomerKey
Relationship type Foreign Key
Relationship name fk FactSurveyResponse.CustomerKey to DimCustomer.CustomerKey
Cardinality 1..n
Unit
Min value
Excl min
Max value
Excl max
Multiple of
Divisible by
Pattern
Enum
Faker function
Sample 16032
Remarks
2.1.2.26.2.4 Column ProductCategoryKey
2.1.2.26.2.4.1 ProductCategoryKey properties
PropertyValue
Business Name ProductCategoryKey
Technical name
Activated true
Id
Type numeric
Subtype int
Default
Default constraint name
Comments
Not null true
Masked with function
Identity
Primary key
Unique
Foreign table DimProductCategory
Foreign field ProductCategoryKey
Relationship type Foreign Key
Relationship name fk FactSurveyResponse.ProductCategoryKey to DimProductCategory.ProductCategoryKey
Cardinality 1..n
Unit
Min value
Excl min
Max value
Excl max
Multiple of
Divisible by
Pattern
Enum
Faker function
Sample 4
Remarks
2.1.2.26.2.5 Column EnglishProductCategoryName
2.1.2.26.2.5.1 EnglishProductCategoryName properties
PropertyValue
Business Name EnglishProductCategoryName
Technical name
Activated true
Id
Type char
Subtype nvarchar
Length 50
Default
Collate
Default constraint name
Comments
Not null true
Masked with function
Primary key
Unique
Foreign table
Foreign field
Relationship type
Relationship name
Cardinality
Min length
Pattern
Format
Enum
Faker function
Sample Accessories
Remarks
2.1.2.26.2.6 Column ProductSubcategoryKey
2.1.2.26.2.6.1 ProductSubcategoryKey properties
PropertyValue
Business Name ProductSubcategoryKey
Technical name
Activated true
Id
Type numeric
Subtype int
Default
Default constraint name
Comments
Not null true
Masked with function
Identity
Primary key
Unique
Foreign table DimProductSubcategory
Foreign field ProductSubcategoryKey
Relationship type Foreign Key
Relationship name fk FactSurveyResponse.ProductSubcategoryKey to DimProductSubcategory.ProductSubcategoryKey
Cardinality 1..n
Unit
Min value
Excl min
Max value
Excl max
Multiple of
Divisible by
Pattern
Enum
Faker function
Sample 26
Remarks
2.1.2.26.2.7 Column EnglishProductSubcategoryName
2.1.2.26.2.7.1 EnglishProductSubcategoryName properties
PropertyValue
Business Name EnglishProductSubcategoryName
Technical name
Activated true
Id
Type char
Subtype nvarchar
Length 50
Default
Collate
Default constraint name
Comments
Not null true
Masked with function
Primary key
Unique
Foreign table
Foreign field
Relationship type
Relationship name
Cardinality
Min length
Pattern
Format
Enum
Faker function
Sample Bike Racks
Remarks
2.1.2.26.3 FactSurveyResponse Composite keys
PropertyValue
Primary key
[1] Constraint name
Key
Unique key
[1] Constraint name
Key
Alternate key
2.1.2.26.4 FactSurveyResponse JSON Schema
{
    "$schema": "http://json-schema.org/draft-04/schema#",
    "type": "object",
    "title": "FactSurveyResponse",
    "additionalProperties": false,
    "properties": {
        "SurveyResponseKey": {
            "type": "number"
        },
        "DateKey": {
            "type": "number"
        },
        "CustomerKey": {
            "type": "number"
        },
        "ProductCategoryKey": {
            "type": "number"
        },
        "EnglishProductCategoryName": {
            "type": "string"
        },
        "ProductSubcategoryKey": {
            "type": "number"
        },
        "EnglishProductSubcategoryName": {
            "type": "string"
        }
    },
    "required": [
        "SurveyResponseKey",
        "DateKey",
        "CustomerKey",
        "ProductCategoryKey",
        "EnglishProductCategoryName",
        "ProductSubcategoryKey",
        "EnglishProductSubcategoryName"
    ]
}
2.1.2.26.5 FactSurveyResponse JSON data
{
    "SurveyResponseKey": 527,
    "DateKey": 20020906,
    "CustomerKey": 16032,
    "ProductCategoryKey": 4,
    "EnglishProductCategoryName": "Accessories",
    "ProductSubcategoryKey": 26,
    "EnglishProductSubcategoryName": "Bike Racks"
}
2.1.2.26.6 FactSurveyResponse Target Script
IF NOT EXISTS (SELECT * FROM sys.databases WHERE name = N'[hacksqlpool]')
begin
	CREATE DATABASE [hacksqlpool];
	USE [hacksqlpool];
end;

CREATE SCHEMA [dbo];

CREATE TABLE [dbo].[FactSurveyResponse] (
	[SurveyResponseKey] INT PRIMARY KEY NONCLUSTERED NOT ENFORCED NOT NULL,
	[DateKey] INT NOT NULL,
	[CustomerKey] INT NOT NULL,
	[ProductCategoryKey] INT NOT NULL,
	[EnglishProductCategoryName] NVARCHAR(50) NOT NULL,
	[ProductSubcategoryKey] INT NOT NULL,
	[EnglishProductSubcategoryName] NVARCHAR(50) NOT NULL
)WITH (
	CLUSTERED COLUMNSTORE INDEX,
	DISTRIBUTION = ROUND_ROBIN
);
2.1.2.27 Table ProspectiveBuyer
2.1.2.27.1 ProspectiveBuyer Properties
PropertyValue
TableProspectiveBuyer
Technical name
Activatedtrue
Id
Schemadbo
Additional propertiesfalse
$ref
Comments
If not exist
Distributionround_robin
Persistenceregular
SQL pool indexingclustered columnstore index
Partition
CTAS query
Range for values
Boundary value
Remarks
2.1.2.27.2 ProspectiveBuyer Column
ColumnTypeReqKeyDescriptionComments
ProspectiveBuyerKeyinttruepk
ProspectAlternateKeynvarchar(15)false
FirstNamenvarchar(50)false
MiddleNamenvarchar(50)false
LastNamenvarchar(50)false
BirthDatedatetimefalse
MaritalStatusnchar(1)false
Gendernvarchar(1)false
EmailAddressnvarchar(50)false
YearlyIncomemoneyfalse
TotalChildrentinyintfalse
NumberChildrenAtHometinyintfalse
Educationnvarchar(40)false
Occupationnvarchar(100)false
HouseOwnerFlagnchar(1)false
NumberCarsOwnedtinyintfalse
AddressLine1nvarchar(120)false
AddressLine2nvarchar(120)false
Citynvarchar(30)false
StateProvinceCodenvarchar(3)false
PostalCodenvarchar(15)false
Phonenvarchar(20)false
Salutationnvarchar(8)false
Unknownintfalse
2.1.2.27.2.1 Column ProspectiveBuyerKey
2.1.2.27.2.1.1 ProspectiveBuyerKey properties
PropertyValue
Business Name ProspectiveBuyerKey
Technical name
Activated true
Id
Type numeric
Subtype int
Default
Default constraint name
Comments
Not null true
Masked with function
Identity
Primary key true
Foreign table
Foreign field
Relationship type
Relationship name
Cardinality
Unit
Min value
Excl min
Max value
Excl max
Multiple of
Divisible by
Pattern
Enum
Faker function
Sample 291
Remarks
2.1.2.27.2.2 Column ProspectAlternateKey
2.1.2.27.2.2.1 ProspectAlternateKey properties
PropertyValue
Business Name ProspectAlternateKey
Technical name
Activated true
Id
Type char
Subtype nvarchar
Length 15
Default
Collate
Default constraint name
Comments
Not null
Masked with function
Primary key
Unique
Foreign table
Foreign field
Relationship type
Relationship name
Cardinality
Min length
Pattern
Format
Enum
Faker function
Sample 7164
Remarks
2.1.2.27.2.3 Column FirstName
2.1.2.27.2.3.1 FirstName properties
PropertyValue
Business Name FirstName
Technical name
Activated true
Id
Type char
Subtype nvarchar
Length 50
Default
Collate
Default constraint name
Comments
Not null
Masked with function
Primary key
Unique
Foreign table
Foreign field
Relationship type
Relationship name
Cardinality
Min length
Pattern
Format
Enum
Faker function
Sample Billy
Remarks
2.1.2.27.2.4 Column MiddleName
2.1.2.27.2.4.1 MiddleName properties
PropertyValue
Business Name MiddleName
Technical name
Activated true
Id
Type char
Subtype nvarchar
Length 50
Default
Collate
Default constraint name
Comments
Not null
Masked with function
Primary key
Unique
Foreign table
Foreign field
Relationship type
Relationship name
Cardinality
Min length
Pattern
Format
Enum
Faker function
Sample D
Remarks
2.1.2.27.2.5 Column LastName
2.1.2.27.2.5.1 LastName properties
PropertyValue
Business Name LastName
Technical name
Activated true
Id
Type char
Subtype nvarchar
Length 50
Default
Collate
Default constraint name
Comments
Not null
Masked with function
Primary key
Unique
Foreign table
Foreign field
Relationship type
Relationship name
Cardinality
Min length
Pattern
Format
Enum
Faker function
Sample Vazquez
Remarks
2.1.2.27.2.6 Column BirthDate
2.1.2.27.2.6.1 BirthDate properties
PropertyValue
Business Name BirthDate
Technical name
Activated true
Id
Type datetime
Subtype datetime
Comments
Not null
Masked with function
Primary key
Unique
Pattern
Default
Default constraint name
Enum
Faker function
Sample 1964-11-11T00:00:00.000Z
Remarks
2.1.2.27.2.7 Column MaritalStatus
2.1.2.27.2.7.1 MaritalStatus properties
PropertyValue
Business Name MaritalStatus
Technical name
Activated true
Id
Type char
Subtype nchar
Length 1
Default
Collate
Default constraint name
Comments
Not null
Masked with function
Primary key
Unique
Foreign table
Foreign field
Relationship type
Relationship name
Cardinality
Min length
Pattern
Format
Enum
Faker function
Sample S
Remarks
2.1.2.27.2.8 Column Gender
2.1.2.27.2.8.1 Gender properties
PropertyValue
Business Name Gender
Technical name
Activated true
Id
Type char
Subtype nvarchar
Length 1
Default
Collate
Default constraint name
Comments
Not null
Masked with function
Primary key
Unique
Foreign table
Foreign field
Relationship type
Relationship name
Cardinality
Min length
Pattern
Format
Enum
Faker function
Sample M
Remarks
2.1.2.27.2.9 Column EmailAddress
2.1.2.27.2.9.1 EmailAddress properties
PropertyValue
Business Name EmailAddress
Technical name
Activated true
Id
Type char
Subtype nvarchar
Length 50
Default
Collate
Default constraint name
Comments
Not null
Masked with function
Primary key
Unique
Foreign table
Foreign field
Relationship type
Relationship name
Cardinality
Min length
Pattern
Format
Enum
Faker function
Sample bvazquez@contoso.com
Remarks
2.1.2.27.2.10 Column YearlyIncome
2.1.2.27.2.10.1 YearlyIncome properties
PropertyValue
Business Name YearlyIncome
Technical name
Activated true
Id
Type numeric
Subtype money
Default
Default constraint name
Comments
Not null
Masked with function
Identity
Primary key
Unique
Foreign table
Foreign field
Relationship type
Relationship name
Cardinality
Unit
Min value
Excl min
Max value
Excl max
Multiple of
Divisible by
Pattern
Enum
Faker function
Sample 80000
Remarks
2.1.2.27.2.11 Column TotalChildren
2.1.2.27.2.11.1 TotalChildren properties
PropertyValue
Business Name TotalChildren
Technical name
Activated true
Id
Type numeric
Subtype tinyint
Default
Default constraint name
Comments
Not null
Masked with function
Identity
Primary key
Unique
Foreign table
Foreign field
Relationship type
Relationship name
Cardinality
Unit
Min value
Excl min
Max value
Excl max
Multiple of
Divisible by
Pattern
Enum
Faker function
Sample 4
Remarks
2.1.2.27.2.12 Column NumberChildrenAtHome
2.1.2.27.2.12.1 NumberChildrenAtHome properties
PropertyValue
Business Name NumberChildrenAtHome
Technical name
Activated true
Id
Type numeric
Subtype tinyint
Default
Default constraint name
Comments
Not null
Masked with function
Identity
Primary key
Unique
Foreign table
Foreign field
Relationship type
Relationship name
Cardinality
Unit
Min value
Excl min
Max value
Excl max
Multiple of
Divisible by
Pattern
Enum
Faker function
Sample 3
Remarks
2.1.2.27.2.13 Column Education
2.1.2.27.2.13.1 Education properties
PropertyValue
Business Name Education
Technical name
Activated true
Id
Type char
Subtype nvarchar
Length 40
Default
Collate
Default constraint name
Comments
Not null
Masked with function
Primary key
Unique
Foreign table
Foreign field
Relationship type
Relationship name
Cardinality
Min length
Pattern
Format
Enum
Faker function
Sample Partial Co
Remarks
2.1.2.27.2.14 Column Occupation
2.1.2.27.2.14.1 Occupation properties
PropertyValue
Business Name Occupation
Technical name
Activated true
Id
Type char
Subtype nvarchar
Length 100
Default
Collate
Default constraint name
Comments
Not null
Masked with function
Primary key
Unique
Foreign table
Foreign field
Relationship type
Relationship name
Cardinality
Min length
Pattern
Format
Enum
Faker function
Sample Professional
Remarks
2.1.2.27.2.15 Column HouseOwnerFlag
2.1.2.27.2.15.1 HouseOwnerFlag properties
PropertyValue
Business Name HouseOwnerFlag
Technical name
Activated true
Id
Type char
Subtype nchar
Length 1
Default
Collate
Default constraint name
Comments
Not null
Masked with function
Primary key
Unique
Foreign table
Foreign field
Relationship type
Relationship name
Cardinality
Min length
Pattern
Format
Enum
Faker function
Sample 1
Remarks
2.1.2.27.2.16 Column NumberCarsOwned
2.1.2.27.2.16.1 NumberCarsOwned properties
PropertyValue
Business Name NumberCarsOwned
Technical name
Activated true
Id
Type numeric
Subtype tinyint
Default
Default constraint name
Comments
Not null
Masked with function
Identity
Primary key
Unique
Foreign table
Foreign field
Relationship type
Relationship name
Cardinality
Unit
Min value
Excl min
Max value
Excl max
Multiple of
Divisible by
Pattern
Enum
Faker function
Sample 1
Remarks
2.1.2.27.2.17 Column AddressLine1
2.1.2.27.2.17.1 AddressLine1 properties
PropertyValue
Business Name AddressLine1
Technical name
Activated true
Id
Type char
Subtype nvarchar
Length 120
Default
Collate
Default constraint name
Comments
Not null
Masked with function
Primary key
Unique
Foreign table
Foreign field
Relationship type
Relationship name
Cardinality
Min length
Pattern
Format
Enum
Faker function
Sample 1880 Birchwood
Remarks
2.1.2.27.2.18 Column AddressLine2
2.1.2.27.2.18.1 AddressLine2 properties
PropertyValue
Business Name AddressLine2
Technical name
Activated true
Id
Type char
Subtype nvarchar
Length 120
Default
Collate
Default constraint name
Comments
Not null
Masked with function
Primary key
Unique
Foreign table
Foreign field
Relationship type
Relationship name
Cardinality
Min length
Pattern
Format
Enum
Faker function
Sample #9
Remarks
2.1.2.27.2.19 Column City
2.1.2.27.2.19.1 City properties
PropertyValue
Business Name City
Technical name
Activated true
Id
Type char
Subtype nvarchar
Length 30
Default
Collate
Default constraint name
Comments
Not null
Masked with function
Primary key
Unique
Foreign table
Foreign field
Relationship type
Relationship name
Cardinality
Min length
Pattern
Format
Enum
Faker function
Sample San Gabriel
Remarks
2.1.2.27.2.20 Column StateProvinceCode
2.1.2.27.2.20.1 StateProvinceCode properties
PropertyValue
Business Name StateProvinceCode
Technical name
Activated true
Id
Type char
Subtype nvarchar
Length 3
Default
Collate
Default constraint name
Comments
Not null
Masked with function
Primary key
Unique
Foreign table
Foreign field
Relationship type
Relationship name
Cardinality
Min length
Pattern
Format
Enum
Faker function
Sample CA
Remarks
2.1.2.27.2.21 Column PostalCode
2.1.2.27.2.21.1 PostalCode properties
PropertyValue
Business Name PostalCode
Technical name
Activated true
Id
Type char
Subtype nvarchar
Length 15
Default
Collate
Default constraint name
Comments
Not null
Masked with function
Primary key
Unique
Foreign table
Foreign field
Relationship type
Relationship name
Cardinality
Min length
Pattern
Format
Enum
Faker function
Sample 91776
Remarks
2.1.2.27.2.22 Column Phone
2.1.2.27.2.22.1 Phone properties
PropertyValue
Business Name Phone
Technical name
Activated true
Id
Type char
Subtype nvarchar
Length 20
Default
Collate
Default constraint name
Comments
Not null
Masked with function
Primary key
Unique
Foreign table
Foreign field
Relationship type
Relationship name
Cardinality
Min length
Pattern
Format
Enum
Faker function
Sample 401-555-0114
Remarks
2.1.2.27.2.23 Column Salutation
2.1.2.27.2.23.1 Salutation properties
PropertyValue
Business Name Salutation
Technical name
Activated true
Id
Type char
Subtype nvarchar
Length 8
Default
Collate
Default constraint name
Comments
Not null
Masked with function
Primary key
Unique
Foreign table
Foreign field
Relationship type
Relationship name
Cardinality
Min length
Pattern
Format
Enum
Faker function
Sample Mr.
Remarks
2.1.2.27.2.24 Column Unknown
2.1.2.27.2.24.1 Unknown properties
PropertyValue
Business Name Unknown
Technical name
Activated true
Id
Type numeric
Subtype int
Default
Default constraint name
Comments
Not null
Masked with function
Identity
Primary key
Unique
Foreign table
Foreign field
Relationship type
Relationship name
Cardinality
Unit
Min value
Excl min
Max value
Excl max
Multiple of
Divisible by
Pattern
Enum
Faker function
Sample 4
Remarks
2.1.2.27.3 ProspectiveBuyer Composite keys
PropertyValue
Primary key
[1] Constraint name
Key
Unique key
[1] Constraint name
Key
Alternate key
2.1.2.27.4 ProspectiveBuyer JSON Schema
{
    "$schema": "http://json-schema.org/draft-04/schema#",
    "type": "object",
    "title": "ProspectiveBuyer",
    "additionalProperties": false,
    "properties": {
        "ProspectiveBuyerKey": {
            "type": "number"
        },
        "ProspectAlternateKey": {
            "type": "string"
        },
        "FirstName": {
            "type": "string"
        },
        "MiddleName": {
            "type": "string"
        },
        "LastName": {
            "type": "string"
        },
        "BirthDate": {
            "type": "string",
            "maxLength": 24
        },
        "MaritalStatus": {
            "type": "string"
        },
        "Gender": {
            "type": "string"
        },
        "EmailAddress": {
            "type": "string"
        },
        "YearlyIncome": {
            "type": "number"
        },
        "TotalChildren": {
            "type": "number"
        },
        "NumberChildrenAtHome": {
            "type": "number"
        },
        "Education": {
            "type": "string"
        },
        "Occupation": {
            "type": "string"
        },
        "HouseOwnerFlag": {
            "type": "string"
        },
        "NumberCarsOwned": {
            "type": "number"
        },
        "AddressLine1": {
            "type": "string"
        },
        "AddressLine2": {
            "type": "string"
        },
        "City": {
            "type": "string"
        },
        "StateProvinceCode": {
            "type": "string"
        },
        "PostalCode": {
            "type": "string"
        },
        "Phone": {
            "type": "string"
        },
        "Salutation": {
            "type": "string"
        },
        "Unknown": {
            "type": "number"
        }
    },
    "required": [
        "ProspectiveBuyerKey"
    ]
}
2.1.2.27.5 ProspectiveBuyer JSON data
{
    "ProspectiveBuyerKey": 291,
    "ProspectAlternateKey": "7164",
    "FirstName": "Billy",
    "MiddleName": "D",
    "LastName": "Vazquez",
    "BirthDate": "1964-11-11T00:00:00.000Z",
    "MaritalStatus": "S",
    "Gender": "M",
    "EmailAddress": "bvazquez@contoso.com",
    "YearlyIncome": 80000,
    "TotalChildren": 4,
    "NumberChildrenAtHome": 3,
    "Education": "Partial Co",
    "Occupation": "Professional",
    "HouseOwnerFlag": "1",
    "NumberCarsOwned": 1,
    "AddressLine1": "1880 Birchwood",
    "AddressLine2": "#9",
    "City": "San Gabriel",
    "StateProvinceCode": "CA",
    "PostalCode": "91776",
    "Phone": "401-555-0114",
    "Salutation": "Mr.",
    "Unknown": 4
}
2.1.2.27.6 ProspectiveBuyer Target Script
IF NOT EXISTS (SELECT * FROM sys.databases WHERE name = N'[hacksqlpool]')
begin
	CREATE DATABASE [hacksqlpool];
	USE [hacksqlpool];
end;

CREATE SCHEMA [dbo];

CREATE TABLE [dbo].[ProspectiveBuyer] (
	[ProspectiveBuyerKey] INT PRIMARY KEY NONCLUSTERED NOT ENFORCED NOT NULL,
	[ProspectAlternateKey] NVARCHAR(15),
	[FirstName] NVARCHAR(50),
	[MiddleName] NVARCHAR(50),
	[LastName] NVARCHAR(50),
	[BirthDate] DATETIME,
	[MaritalStatus] NCHAR(1),
	[Gender] NVARCHAR(1),
	[EmailAddress] NVARCHAR(50),
	[YearlyIncome] MONEY,
	[TotalChildren] TINYINT,
	[NumberChildrenAtHome] TINYINT,
	[Education] NVARCHAR(40),
	[Occupation] NVARCHAR(100),
	[HouseOwnerFlag] NCHAR(1),
	[NumberCarsOwned] TINYINT,
	[AddressLine1] NVARCHAR(120),
	[AddressLine2] NVARCHAR(120),
	[City] NVARCHAR(30),
	[StateProvinceCode] NVARCHAR(3),
	[PostalCode] NVARCHAR(15),
	[Phone] NVARCHAR(20),
	[Salutation] NVARCHAR(8),
	[Unknown] INT
)WITH (
	CLUSTERED COLUMNSTORE INDEX,
	DISTRIBUTION = ROUND_ROBIN
);
2.1.3 dbo Target Script
IF NOT EXISTS (SELECT * FROM sys.databases WHERE name = N'[hacksqlpool]')
begin
	CREATE DATABASE [hacksqlpool];
	USE [hacksqlpool];
end;

CREATE SCHEMA [dbo];

CREATE TABLE [dbo].[AdventureWorksDWBuildVersion] (
	[DBVersion] NVARCHAR(50) PRIMARY KEY NONCLUSTERED NOT ENFORCED NOT NULL,
	[VersionDate] DATETIME NOT NULL
)WITH (
	CLUSTERED COLUMNSTORE INDEX,
	DISTRIBUTION = ROUND_ROBIN
);

CREATE TABLE [dbo].[DatabaseLog] (
	[DatabaseLogID] INT PRIMARY KEY NONCLUSTERED NOT ENFORCED NOT NULL,
	[PostTime] DATETIME NOT NULL,
	[DatabaseUser] NVARCHAR(128) NOT NULL,
	[Event] NVARCHAR(128) NOT NULL,
	[Schema] NVARCHAR(128),
	[Object] NVARCHAR(128),
	[TSQL] NVARCHAR(4000) NOT NULL
)WITH (
	CLUSTERED COLUMNSTORE INDEX,
	DISTRIBUTION = ROUND_ROBIN
);

CREATE TABLE [dbo].[DimAccount] (
	[AccountKey] INT PRIMARY KEY NONCLUSTERED NOT ENFORCED NOT NULL,
	[ParentAccountKey] INT,
	[AccountCodeAlternateKey] INT,
	[ParentAccountCodeAlternateKey] INT,
	[AccountDescription] NVARCHAR(50),
	[AccountType] NVARCHAR(50),
	[Operator] NVARCHAR(50),
	[CustomMembers] NVARCHAR(300),
	[ValueType] NVARCHAR(50),
	[CustomMemberOptions] NVARCHAR(200)
)WITH (
	CLUSTERED COLUMNSTORE INDEX,
	DISTRIBUTION = ROUND_ROBIN
);

CREATE TABLE [dbo].[DimCurrency] (
	[CurrencyKey] INT PRIMARY KEY NONCLUSTERED NOT ENFORCED NOT NULL,
	[CurrencyAlternateKey] NCHAR(3) NOT NULL,
	[CurrencyName] NVARCHAR(50) NOT NULL
)WITH (
	CLUSTERED COLUMNSTORE INDEX,
	DISTRIBUTION = ROUND_ROBIN
);

CREATE TABLE [dbo].[DimSalesTerritory] (
	[SalesTerritoryKey] INT PRIMARY KEY NONCLUSTERED NOT ENFORCED NOT NULL,
	[SalesTerritoryAlternateKey] INT,
	[SalesTerritoryRegion] NVARCHAR(50) NOT NULL,
	[SalesTerritoryCountry] NVARCHAR(50) NOT NULL,
	[SalesTerritoryGroup] NVARCHAR(50)
)WITH (
	CLUSTERED COLUMNSTORE INDEX,
	DISTRIBUTION = ROUND_ROBIN
);

CREATE TABLE [dbo].[DimGeography] (
	[GeographyKey] INT PRIMARY KEY NONCLUSTERED NOT ENFORCED NOT NULL,
	[City] NVARCHAR(30),
	[StateProvinceCode] NVARCHAR(3),
	[StateProvinceName] NVARCHAR(50),
	[CountryRegionCode] NVARCHAR(3),
	[EnglishCountryRegionName] NVARCHAR(50),
	[SpanishCountryRegionName] NVARCHAR(50),
	[FrenchCountryRegionName] NVARCHAR(50),
	[PostalCode] NVARCHAR(15),
	[SalesTerritoryKey] INT
)WITH (
	CLUSTERED COLUMNSTORE INDEX,
	DISTRIBUTION = ROUND_ROBIN
);

CREATE TABLE [dbo].[DimCustomer] (
	[CustomerKey] INT PRIMARY KEY NONCLUSTERED NOT ENFORCED NOT NULL,
	[GeographyKey] INT,
	[CustomerAlternateKey] NVARCHAR(15) NOT NULL,
	[Title] NVARCHAR(8),
	[FirstName] NVARCHAR(50),
	[MiddleName] NVARCHAR(50),
	[LastName] NVARCHAR(50),
	[NameStyle] BIT,
	[BirthDate] DATE,
	[MaritalStatus] NCHAR(1),
	[Suffix] NVARCHAR(10),
	[Gender] NVARCHAR(1),
	[EmailAddress] NVARCHAR(50),
	[YearlyIncome] MONEY,
	[TotalChildren] TINYINT,
	[NumberChildrenAtHome] TINYINT,
	[EnglishEducation] NVARCHAR(40),
	[SpanishEducation] NVARCHAR(40),
	[FrenchEducation] NVARCHAR(40),
	[EnglishOccupation] NVARCHAR(100),
	[SpanishOccupation] NVARCHAR(100),
	[FrenchOccupation] NVARCHAR(100),
	[HouseOwnerFlag] NCHAR(1),
	[NumberCarsOwned] TINYINT,
	[AddressLine1] NVARCHAR(120),
	[AddressLine2] NVARCHAR(120),
	[Phone] NVARCHAR(20),
	[DateFirstPurchase] DATE,
	[CommuteDistance] NVARCHAR(15)
)WITH (
	CLUSTERED COLUMNSTORE INDEX,
	DISTRIBUTION = ROUND_ROBIN
);

CREATE TABLE [dbo].[DimDate] (
	[DateKey] INT PRIMARY KEY NONCLUSTERED NOT ENFORCED NOT NULL,
	[FullDateAlternateKey] DATE NOT NULL,
	[DayNumberOfWeek] TINYINT NOT NULL,
	[EnglishDayNameOfWeek] NVARCHAR(10) NOT NULL,
	[SpanishDayNameOfWeek] NVARCHAR(10) NOT NULL,
	[FrenchDayNameOfWeek] NVARCHAR(10) NOT NULL,
	[DayNumberOfMonth] TINYINT NOT NULL,
	[DayNumberOfYear] SMALLINT NOT NULL,
	[WeekNumberOfYear] TINYINT NOT NULL,
	[EnglishMonthName] NVARCHAR(10) NOT NULL,
	[SpanishMonthName] NVARCHAR(10) NOT NULL,
	[FrenchMonthName] NVARCHAR(10) NOT NULL,
	[MonthNumberOfYear] TINYINT NOT NULL,
	[CalendarQuarter] TINYINT NOT NULL,
	[CalendarYear] SMALLINT NOT NULL,
	[CalendarSemester] TINYINT NOT NULL,
	[FiscalQuarter] TINYINT NOT NULL,
	[FiscalYear] SMALLINT NOT NULL,
	[FiscalSemester] TINYINT NOT NULL
)WITH (
	CLUSTERED COLUMNSTORE INDEX,
	DISTRIBUTION = ROUND_ROBIN
);

CREATE TABLE [dbo].[DimDepartmentGroup] (
	[DepartmentGroupKey] INT PRIMARY KEY NONCLUSTERED NOT ENFORCED NOT NULL,
	[ParentDepartmentGroupKey] INT,
	[DepartmentGroupName] NVARCHAR(50)
)WITH (
	CLUSTERED COLUMNSTORE INDEX,
	DISTRIBUTION = ROUND_ROBIN
);

CREATE TABLE [dbo].[DimEmployee] (
	[EmployeeKey] INT PRIMARY KEY NONCLUSTERED NOT ENFORCED NOT NULL,
	[ParentEmployeeKey] INT,
	[EmployeeNationalIDAlternateKey] NVARCHAR(15),
	[ParentEmployeeNationalIDAlternateKey] NVARCHAR(15),
	[SalesTerritoryKey] INT,
	[FirstName] NVARCHAR(50) NOT NULL,
	[LastName] NVARCHAR(50) NOT NULL,
	[MiddleName] NVARCHAR(50),
	[NameStyle] BIT NOT NULL,
	[Title] NVARCHAR(50),
	[HireDate] DATE,
	[BirthDate] DATE,
	[LoginID] NVARCHAR(256),
	[EmailAddress] NVARCHAR(50),
	[Phone] NVARCHAR(25),
	[MaritalStatus] NCHAR(1),
	[EmergencyContactName] NVARCHAR(50),
	[EmergencyContactPhone] NVARCHAR(25),
	[SalariedFlag] BIT,
	[Gender] NCHAR(1),
	[PayFrequency] TINYINT,
	[BaseRate] MONEY,
	[VacationHours] SMALLINT,
	[SickLeaveHours] SMALLINT,
	[CurrentFlag] BIT NOT NULL,
	[SalesPersonFlag] BIT NOT NULL,
	[DepartmentName] NVARCHAR(50),
	[StartDate] DATE,
	[EndDate] DATE,
	[Status] NVARCHAR(50)
)WITH (
	CLUSTERED COLUMNSTORE INDEX,
	DISTRIBUTION = ROUND_ROBIN
);

CREATE TABLE [dbo].[DimOrganization] (
	[OrganizationKey] INT PRIMARY KEY NONCLUSTERED NOT ENFORCED NOT NULL,
	[ParentOrganizationKey] INT,
	[PercentageOfOwnership] NVARCHAR(16),
	[OrganizationName] NVARCHAR(50),
	[CurrencyKey] INT
)WITH (
	CLUSTERED COLUMNSTORE INDEX,
	DISTRIBUTION = ROUND_ROBIN
);

CREATE TABLE [dbo].[DimProductCategory] (
	[ProductCategoryKey] INT PRIMARY KEY NONCLUSTERED NOT ENFORCED NOT NULL,
	[ProductCategoryAlternateKey] INT,
	[EnglishProductCategoryName] NVARCHAR(50) NOT NULL,
	[SpanishProductCategoryName] NVARCHAR(50) NOT NULL,
	[FrenchProductCategoryName] NVARCHAR(50) NOT NULL
)WITH (
	CLUSTERED COLUMNSTORE INDEX,
	DISTRIBUTION = ROUND_ROBIN
);

CREATE TABLE [dbo].[DimProductSubcategory] (
	[ProductSubcategoryKey] INT PRIMARY KEY NONCLUSTERED NOT ENFORCED NOT NULL,
	[ProductSubcategoryAlternateKey] INT,
	[EnglishProductSubcategoryName] NVARCHAR(50) NOT NULL,
	[SpanishProductSubcategoryName] NVARCHAR(50) NOT NULL,
	[FrenchProductSubcategoryName] NVARCHAR(50) NOT NULL,
	[ProductCategoryKey] INT
)WITH (
	CLUSTERED COLUMNSTORE INDEX,
	DISTRIBUTION = ROUND_ROBIN
);

CREATE TABLE [dbo].[DimProduct] (
	[ProductKey] INT PRIMARY KEY NONCLUSTERED NOT ENFORCED NOT NULL,
	[ProductAlternateKey] NVARCHAR(25),
	[ProductSubcategoryKey] INT,
	[WeightUnitMeasureCode] NCHAR(3),
	[SizeUnitMeasureCode] NCHAR(3),
	[EnglishProductName] NVARCHAR(50) NOT NULL,
	[SpanishProductName] NVARCHAR(50),
	[FrenchProductName] NVARCHAR(50),
	[StandardCost] MONEY,
	[FinishedGoodsFlag] BIT NOT NULL,
	[Color] NVARCHAR(15) NOT NULL,
	[SafetyStockLevel] SMALLINT,
	[ReorderPoint] SMALLINT,
	[ListPrice] MONEY,
	[Size] NVARCHAR(50),
	[SizeRange] NVARCHAR(50),
	[Weight] FLOAT,
	[DaysToManufacture] INT,
	[ProductLine] NCHAR(2),
	[DealerPrice] MONEY,
	[Class] NCHAR(2),
	[Style] NCHAR(2),
	[ModelName] NVARCHAR(50),
	[EnglishDescription] NVARCHAR(400),
	[FrenchDescription] NVARCHAR(400),
	[ChineseDescription] NVARCHAR(400),
	[ArabicDescription] NVARCHAR(400),
	[HebrewDescription] NVARCHAR(400),
	[ThaiDescription] NVARCHAR(400),
	[GermanDescription] NVARCHAR(400),
	[JapaneseDescription] NVARCHAR(400),
	[TurkishDescription] NVARCHAR(400),
	[StartDate] DATETIME,
	[EndDate] DATETIME,
	[Status] NVARCHAR(7)
)WITH (
	CLUSTERED COLUMNSTORE INDEX,
	DISTRIBUTION = ROUND_ROBIN
);

CREATE TABLE [dbo].[DimPromotion] (
	[PromotionKey] INT PRIMARY KEY NONCLUSTERED NOT ENFORCED NOT NULL,
	[PromotionAlternateKey] INT,
	[EnglishPromotionName] NVARCHAR(255),
	[SpanishPromotionName] NVARCHAR(255),
	[FrenchPromotionName] NVARCHAR(255),
	[DiscountPct] FLOAT,
	[EnglishPromotionType] NVARCHAR(50),
	[SpanishPromotionType] NVARCHAR(50),
	[FrenchPromotionType] NVARCHAR(50),
	[EnglishPromotionCategory] NVARCHAR(50),
	[SpanishPromotionCategory] NVARCHAR(50),
	[FrenchPromotionCategory] NVARCHAR(50),
	[StartDate] DATETIME NOT NULL,
	[EndDate] DATETIME,
	[MinQty] INT,
	[MaxQty] INT
)WITH (
	CLUSTERED COLUMNSTORE INDEX,
	DISTRIBUTION = ROUND_ROBIN
);

CREATE TABLE [dbo].[DimReseller] (
	[ResellerKey] INT PRIMARY KEY NONCLUSTERED NOT ENFORCED NOT NULL,
	[GeographyKey] INT,
	[ResellerAlternateKey] NVARCHAR(15),
	[Phone] NVARCHAR(25),
	[BusinessType] VARCHAR(20) NOT NULL,
	[ResellerName] NVARCHAR(50) NOT NULL,
	[NumberEmployees] INT,
	[OrderFrequency] CHAR(1),
	[OrderMonth] TINYINT,
	[FirstOrderYear] INT,
	[LastOrderYear] INT,
	[ProductLine] NVARCHAR(50),
	[AddressLine1] NVARCHAR(60),
	[AddressLine2] NVARCHAR(60),
	[AnnualSales] MONEY,
	[BankName] NVARCHAR(50),
	[MinPaymentType] TINYINT,
	[MinPaymentAmount] MONEY,
	[AnnualRevenue] MONEY,
	[YearOpened] INT
)WITH (
	CLUSTERED COLUMNSTORE INDEX,
	DISTRIBUTION = ROUND_ROBIN
);

CREATE TABLE [dbo].[DimSalesReason] (
	[SalesReasonKey] INT PRIMARY KEY NONCLUSTERED NOT ENFORCED NOT NULL,
	[SalesReasonAlternateKey] INT NOT NULL,
	[SalesReasonName] NVARCHAR(50) NOT NULL,
	[SalesReasonReasonType] NVARCHAR(50) NOT NULL
)WITH (
	CLUSTERED COLUMNSTORE INDEX,
	DISTRIBUTION = ROUND_ROBIN
);

CREATE TABLE [dbo].[DimScenario] (
	[ScenarioKey] INT PRIMARY KEY NONCLUSTERED NOT ENFORCED NOT NULL,
	[ScenarioName] NVARCHAR(50)
)WITH (
	CLUSTERED COLUMNSTORE INDEX,
	DISTRIBUTION = ROUND_ROBIN
);

CREATE TABLE [dbo].[FactCallCenter] (
	[FactCallCenterID] INT PRIMARY KEY NONCLUSTERED NOT ENFORCED NOT NULL,
	[DateKey] INT NOT NULL,
	[WageType] NVARCHAR(15) NOT NULL,
	[Shift] NVARCHAR(20) NOT NULL,
	[LevelOneOperators] SMALLINT NOT NULL,
	[LevelTwoOperators] SMALLINT NOT NULL,
	[TotalOperators] SMALLINT NOT NULL,
	[Calls] INT NOT NULL,
	[AutomaticResponses] INT NOT NULL,
	[Orders] INT NOT NULL,
	[IssuesRaised] SMALLINT NOT NULL,
	[AverageTimePerIssue] SMALLINT NOT NULL,
	[ServiceGrade] FLOAT NOT NULL
)WITH (
	CLUSTERED COLUMNSTORE INDEX,
	DISTRIBUTION = ROUND_ROBIN
);

CREATE TABLE [dbo].[FactCurrencyRate] (
	[CurrencyKey] INT PRIMARY KEY NONCLUSTERED NOT ENFORCED NOT NULL,
	[DateKey] INT NOT NULL,
	[AverageRate] FLOAT NOT NULL,
	[EndOfDayRate] FLOAT NOT NULL
)WITH (
	CLUSTERED COLUMNSTORE INDEX,
	DISTRIBUTION = ROUND_ROBIN
);

CREATE TABLE [dbo].[FactFinance] (
	[FinanceKey] INT PRIMARY KEY NONCLUSTERED NOT ENFORCED NOT NULL,
	[DateKey] INT NOT NULL,
	[OrganizationKey] INT NOT NULL,
	[DepartmentGroupKey] INT NOT NULL,
	[ScenarioKey] INT NOT NULL,
	[AccountKey] INT NOT NULL,
	[Amount] FLOAT NOT NULL
)WITH (
	CLUSTERED COLUMNSTORE INDEX,
	DISTRIBUTION = ROUND_ROBIN
);

CREATE TABLE [dbo].[FactInternetSalesReason] (
	[SalesOrderNumber] NVARCHAR(20) PRIMARY KEY NONCLUSTERED NOT ENFORCED NOT NULL,
	[SalesOrderLineNumber] TINYINT NOT NULL,
	[SalesReasonKey] INT NOT NULL
)WITH (
	CLUSTERED COLUMNSTORE INDEX,
	DISTRIBUTION = ROUND_ROBIN
);

CREATE TABLE [dbo].[FactInternetSales] (
	[ProductKey] INT PRIMARY KEY NONCLUSTERED NOT ENFORCED NOT NULL,
	[OrderDateKey] INT NOT NULL,
	[DueDateKey] INT NOT NULL,
	[ShipDateKey] INT NOT NULL,
	[CustomerKey] INT NOT NULL,
	[PromotionKey] INT NOT NULL,
	[CurrencyKey] INT NOT NULL,
	[SalesTerritoryKey] INT NOT NULL,
	[SalesOrderNumber] NVARCHAR(20) NOT NULL,
	[SalesOrderLineNumber] TINYINT NOT NULL,
	[RevisionNumber] TINYINT NOT NULL,
	[OrderQuantity] SMALLINT NOT NULL,
	[UnitPrice] MONEY NOT NULL,
	[ExtendedAmount] MONEY NOT NULL,
	[UnitPriceDiscountPct] FLOAT NOT NULL,
	[DiscountAmount] FLOAT NOT NULL,
	[ProductStandardCost] MONEY NOT NULL,
	[TotalProductCost] MONEY NOT NULL,
	[SalesAmount] MONEY NOT NULL,
	[TaxAmt] MONEY NOT NULL,
	[Freight] MONEY NOT NULL,
	[CarrierTrackingNumber] NVARCHAR(25),
	[CustomerPONumber] NVARCHAR(25)
)WITH (
	CLUSTERED COLUMNSTORE INDEX,
	DISTRIBUTION = ROUND_ROBIN
);

CREATE TABLE [dbo].[FactResellerSales] (
	[ProductKey] INT PRIMARY KEY NONCLUSTERED NOT ENFORCED NOT NULL,
	[OrderDateKey] INT NOT NULL,
	[DueDateKey] INT NOT NULL,
	[ShipDateKey] INT NOT NULL,
	[ResellerKey] INT NOT NULL,
	[EmployeeKey] INT NOT NULL,
	[PromotionKey] INT NOT NULL,
	[CurrencyKey] INT NOT NULL,
	[SalesTerritoryKey] INT NOT NULL,
	[SalesOrderNumber] NVARCHAR(20) NOT NULL,
	[SalesOrderLineNumber] TINYINT NOT NULL,
	[RevisionNumber] TINYINT,
	[OrderQuantity] SMALLINT,
	[UnitPrice] MONEY,
	[ExtendedAmount] MONEY,
	[UnitPriceDiscountPct] FLOAT,
	[DiscountAmount] FLOAT,
	[ProductStandardCost] MONEY,
	[TotalProductCost] MONEY,
	[SalesAmount] MONEY,
	[TaxAmt] MONEY,
	[Freight] MONEY,
	[CarrierTrackingNumber] NVARCHAR(25),
	[CustomerPONumber] NVARCHAR(25)
)WITH (
	CLUSTERED COLUMNSTORE INDEX,
	DISTRIBUTION = ROUND_ROBIN
);

CREATE TABLE [dbo].[FactSalesQuota] (
	[SalesQuotaKey] INT PRIMARY KEY NONCLUSTERED NOT ENFORCED NOT NULL,
	[EmployeeKey] INT NOT NULL,
	[DateKey] INT NOT NULL,
	[CalendarYear] SMALLINT NOT NULL,
	[CalendarQuarter] TINYINT NOT NULL,
	[SalesAmountQuota] MONEY NOT NULL
)WITH (
	CLUSTERED COLUMNSTORE INDEX,
	DISTRIBUTION = ROUND_ROBIN
);

CREATE TABLE [dbo].[FactSurveyResponse] (
	[SurveyResponseKey] INT PRIMARY KEY NONCLUSTERED NOT ENFORCED NOT NULL,
	[DateKey] INT NOT NULL,
	[CustomerKey] INT NOT NULL,
	[ProductCategoryKey] INT NOT NULL,
	[EnglishProductCategoryName] NVARCHAR(50) NOT NULL,
	[ProductSubcategoryKey] INT NOT NULL,
	[EnglishProductSubcategoryName] NVARCHAR(50) NOT NULL
)WITH (
	CLUSTERED COLUMNSTORE INDEX,
	DISTRIBUTION = ROUND_ROBIN
);

CREATE TABLE [dbo].[ProspectiveBuyer] (
	[ProspectiveBuyerKey] INT PRIMARY KEY NONCLUSTERED NOT ENFORCED NOT NULL,
	[ProspectAlternateKey] NVARCHAR(15),
	[FirstName] NVARCHAR(50),
	[MiddleName] NVARCHAR(50),
	[LastName] NVARCHAR(50),
	[BirthDate] DATETIME,
	[MaritalStatus] NCHAR(1),
	[Gender] NVARCHAR(1),
	[EmailAddress] NVARCHAR(50),
	[YearlyIncome] MONEY,
	[TotalChildren] TINYINT,
	[NumberChildrenAtHome] TINYINT,
	[Education] NVARCHAR(40),
	[Occupation] NVARCHAR(100),
	[HouseOwnerFlag] NCHAR(1),
	[NumberCarsOwned] TINYINT,
	[AddressLine1] NVARCHAR(120),
	[AddressLine2] NVARCHAR(120),
	[City] NVARCHAR(30),
	[StateProvinceCode] NVARCHAR(3),
	[PostalCode] NVARCHAR(15),
	[Phone] NVARCHAR(20),
	[Salutation] NVARCHAR(8),
	[Unknown] INT
)WITH (
	CLUSTERED COLUMNSTORE INDEX,
	DISTRIBUTION = ROUND_ROBIN
);

CREATE VIEW [dbo].[AggregateSales]
AS SELECT FIS.SalesAmount, DG.PostalCode, DC.YearlyIncome AS CustomerIncome,  DD.FullDateAlternateKey AS OrderDate
			FROM FactInternetSales AS FIS  
				   JOIN DimCustomer AS DC 
				   ON (FIS.CustomerKey = DC.CustomerKey)
				   JOIN DimDate AS DD
				   ON (FIS.OrderDateKey = DD.DateKey)
				   JOIN DimGeography AS DG 
						 ON (DC.GeographyKey = DG.GeographyKey);
;

CREATE VIEW [dbo].[SalesByCustomer]
AS SELECT fis.SalesAmount, dst.Gender, dst.NumberCarsOwned, dst.YearlyIncome AS CustomerYearlyIncome, dst.TotalChildren
	      FROM FactInternetSales AS fis
	      LEFT OUTER JOIN DimCustomer AS dst
	      ON (fis.CustomerKey=dst.CustomerKey);
;

CREATE VIEW [dbo].[vDMPrep]
AS SELECT
					pc.[EnglishProductCategoryName]
					,Coalesce(p.[ModelName], p.[EnglishProductName]) AS [Model]
					,c.[CustomerKey]
					,s.[SalesTerritoryGroup] AS [Region]
					,CASE
						WHEN Month(GetDate()) < Month(c.[BirthDate])
							THEN DateDiff(yy,c.[BirthDate],GetDate()) - 1
						WHEN Month(GetDate()) = Month(c.[BirthDate])
						AND Day(GetDate()) < Day(c.[BirthDate])
							THEN DateDiff(yy,c.[BirthDate],GetDate()) - 1
						ELSE DateDiff(yy,c.[BirthDate],GetDate())
					END AS [Age]
					,CASE
						WHEN c.[YearlyIncome] < 40000 THEN 'Low'
						WHEN c.[YearlyIncome] > 60000 THEN 'High'
						ELSE 'Moderate'
					END AS [IncomeGroup]
					,d.[CalendarYear]
					,d.[FiscalYear]
					,d.[MonthNumberOfYear] AS [Month]
					,f.[SalesOrderNumber] AS [OrderNumber]
					,f.SalesOrderLineNumber AS LineNumber
					,f.OrderQuantity AS Quantity
					,f.ExtendedAmount AS Amount  
				FROM
					[dbo].[FactInternetSales] f
				INNER JOIN [dbo].[DimDate] d
					ON f.[OrderDateKey] = d.[DateKey]
				INNER JOIN [dbo].[DimProduct] p
					ON f.[ProductKey] = p.[ProductKey]
				INNER JOIN [dbo].[DimProductSubcategory] psc
					ON p.[ProductSubcategoryKey] = psc.[ProductSubcategoryKey]
				INNER JOIN [dbo].[DimProductCategory] pc
					ON psc.[ProductCategoryKey] = pc.[ProductCategoryKey]
				INNER JOIN [dbo].[DimCustomer] c
					ON f.[CustomerKey] = c.[CustomerKey]
				INNER JOIN [dbo].[DimGeography] g
					ON c.[GeographyKey] = g.[GeographyKey]
				INNER JOIN [dbo].[DimSalesTerritory] s
					ON g.[SalesTerritoryKey] = s.[SalesTerritoryKey];
;

CREATE VIEW [dbo].[SalesByDate]
AS SELECT fis.SalesAmount, dst.EnglishMonthName, dst.CalendarYear
	      FROM FactInternetSales AS fis
	      LEFT OUTER JOIN DimDate AS dst
	      ON (fis.OrderDateKey=dst.DateKey);
;

CREATE VIEW [dbo].[SalesByRegion]
AS SELECT fis.SalesAmount, dst.PostalCode, dst.StateProvinceCode
	      FROM FactInternetSales AS fis
	      LEFT OUTER JOIN DimGeography AS dst
	      ON (fis.SalesTerritoryKey=dst.SalesTerritoryKey);
;

CREATE VIEW [dbo].[SalesByCategory]
AS SELECT fis.SalesAmount, dst.ProductLine
	      FROM FactInternetSales AS fis
	      LEFT OUTER JOIN DimProduct AS dst
	      ON (fis.ProductKey=dst.ProductKey);
;
3. Relationships
3.1 Relationship fk DimAccount.AccountKey to FactFinance.AccountKey
3.1.1 fk DimAccount.AccountKey to FactFinance.AccountKey Diagram
Parent TableParent Column
DimAccount AccountKey
Child TableChild Column
FactFinance AccountKey
3.1.2 fk DimAccount.AccountKey to FactFinance.AccountKey Properties
PropertyValue
Name fk DimAccount.AccountKey to FactFinance.AccountKey
Description
Parent TableDimAccount
Parent ColumnAccountKey
Parent Cardinality 1
Child TableFactFinance
Child ColumnAccountKey
Child Cardinality 0..n
Comments
3.2 Relationship fk DimAccount.ParentAccountCodeAlternateKey to DimAccount.AccountKey
3.2.1 fk DimAccount.ParentAccountCodeAlternateKey to DimAccount.AccountKey Diagram
Parent TableParent Column
DimAccount AccountKey
Child TableChild Column
DimAccount ParentAccountCodeAlternateKey
3.2.2 fk DimAccount.ParentAccountCodeAlternateKey to DimAccount.AccountKey Properties
PropertyValue
Name fk DimAccount.ParentAccountCodeAlternateKey to DimAccount.AccountKey
Description
Parent TableDimAccount
Parent ColumnAccountKey
Parent Cardinality 1
Child TableDimAccount
Child ColumnParentAccountCodeAlternateKey
Child Cardinality 1..n
Comments
3.3 Relationship fk DimAccount.ParentAccountKey to DimAccount.AccountKey
3.3.1 fk DimAccount.ParentAccountKey to DimAccount.AccountKey Diagram
Parent TableParent Column
DimAccount AccountKey
Child TableChild Column
DimAccount ParentAccountKey
3.3.2 fk DimAccount.ParentAccountKey to DimAccount.AccountKey Properties
PropertyValue
Name fk DimAccount.ParentAccountKey to DimAccount.AccountKey
Description
Parent TableDimAccount
Parent ColumnAccountKey
Parent Cardinality 1
Child TableDimAccount
Child ColumnParentAccountKey
Child Cardinality 1..n
Comments
3.4 Relationship fk DimCurrency.CurrencyKey to FactCurrencyRate.CurrencyKey
3.4.1 fk DimCurrency.CurrencyKey to FactCurrencyRate.CurrencyKey Diagram
Parent TableParent Column
DimCurrency CurrencyKey
Child TableChild Column
FactCurrencyRate CurrencyKey
3.4.2 fk DimCurrency.CurrencyKey to FactCurrencyRate.CurrencyKey Properties
PropertyValue
Name fk DimCurrency.CurrencyKey to FactCurrencyRate.CurrencyKey
Description
Parent TableDimCurrency
Parent ColumnCurrencyKey
Parent Cardinality 1
Child TableFactCurrencyRate
Child ColumnCurrencyKey
Child Cardinality 0..n
Comments
3.5 Relationship fk DimCustomer.GeographyKey to DimGeography.GeographyKey
3.5.1 fk DimCustomer.GeographyKey to DimGeography.GeographyKey Diagram
Parent TableParent Column
DimGeography GeographyKey
Child TableChild Column
DimCustomer GeographyKey
3.5.2 fk DimCustomer.GeographyKey to DimGeography.GeographyKey Properties
PropertyValue
Name fk DimCustomer.GeographyKey to DimGeography.GeographyKey
Description
Parent TableDimGeography
Parent ColumnGeographyKey
Parent Cardinality 1
Child TableDimCustomer
Child ColumnGeographyKey
Child Cardinality 1..n
Comments
3.6 Relationship fk DimDate.DateKey to FactCallCenter.DateKey
3.6.1 fk DimDate.DateKey to FactCallCenter.DateKey Diagram
Parent TableParent Column
DimDate DateKey
Child TableChild Column
FactCallCenter DateKey
3.6.2 fk DimDate.DateKey to FactCallCenter.DateKey Properties
PropertyValue
Name fk DimDate.DateKey to FactCallCenter.DateKey
Description
Parent TableDimDate
Parent ColumnDateKey
Parent Cardinality 1
Child TableFactCallCenter
Child ColumnDateKey
Child Cardinality 0..n
Comments
3.7 Relationship fk DimDate.DateKey to FactCurrencyRate.DateKey
3.7.1 fk DimDate.DateKey to FactCurrencyRate.DateKey Diagram
Parent TableParent Column
DimDate DateKey
Child TableChild Column
FactCurrencyRate DateKey
3.7.2 fk DimDate.DateKey to FactCurrencyRate.DateKey Properties
PropertyValue
Name fk DimDate.DateKey to FactCurrencyRate.DateKey
Description
Parent TableDimDate
Parent ColumnDateKey
Parent Cardinality 1
Child TableFactCurrencyRate
Child ColumnDateKey
Child Cardinality 0..n
Comments
3.8 Relationship fk DimDate.DateKey to FactFinance.DateKey
3.8.1 fk DimDate.DateKey to FactFinance.DateKey Diagram
Parent TableParent Column
DimDate DateKey
Child TableChild Column
FactFinance DateKey
3.8.2 fk DimDate.DateKey to FactFinance.DateKey Properties
PropertyValue
Name fk DimDate.DateKey to FactFinance.DateKey
Description
Parent TableDimDate
Parent ColumnDateKey
Parent Cardinality 1
Child TableFactFinance
Child ColumnDateKey
Child Cardinality 0..n
Comments
3.9 Relationship fk DimDepartmentGroup.DepartmentGroupKey to DimDepartmentGroup.ParentDepartmentGroupKey
3.9.1 fk DimDepartmentGroup.DepartmentGroupKey to DimDepartmentGroup.ParentDepartmentGroupKey Diagram
3.9.2 fk DimDepartmentGroup.DepartmentGroupKey to DimDepartmentGroup.ParentDepartmentGroupKey Properties
PropertyValue
Name fk DimDepartmentGroup.DepartmentGroupKey to DimDepartmentGroup.ParentDepartmentGroupKey
Description
Parent TableDimDepartmentGroup
Parent ColumnDepartmentGroupKey
Parent Cardinality 1
Child TableDimDepartmentGroup
Child ColumnParentDepartmentGroupKey
Child Cardinality 0..n
Comments
3.10 Relationship fk DimDepartmentGroup.DepartmentGroupKey to FactFinance.DepartmentGroupKey
3.10.1 fk DimDepartmentGroup.DepartmentGroupKey to FactFinance.DepartmentGroupKey Diagram
Parent TableParent Column
DimDepartmentGroup DepartmentGroupKey
Child TableChild Column
FactFinance DepartmentGroupKey
3.10.2 fk DimDepartmentGroup.DepartmentGroupKey to FactFinance.DepartmentGroupKey Properties
PropertyValue
Name fk DimDepartmentGroup.DepartmentGroupKey to FactFinance.DepartmentGroupKey
Description
Parent TableDimDepartmentGroup
Parent ColumnDepartmentGroupKey
Parent Cardinality 1
Child TableFactFinance
Child ColumnDepartmentGroupKey
Child Cardinality 0..n
Comments
3.11 Relationship fk DimEmployee.ParentEmployeeKey to DimEmployee.EmployeeKey
3.11.1 fk DimEmployee.ParentEmployeeKey to DimEmployee.EmployeeKey Diagram
Parent TableParent Column
DimEmployee EmployeeKey
Child TableChild Column
DimEmployee ParentEmployeeKey
3.11.2 fk DimEmployee.ParentEmployeeKey to DimEmployee.EmployeeKey Properties
PropertyValue
Name fk DimEmployee.ParentEmployeeKey to DimEmployee.EmployeeKey
Description
Parent TableDimEmployee
Parent ColumnEmployeeKey
Parent Cardinality 1
Child TableDimEmployee
Child ColumnParentEmployeeKey
Child Cardinality 1..n
Comments
3.12 Relationship fk DimEmployee.SalesTerritoryKey to DimSalesTerritory.SalesTerritoryKey
3.12.1 fk DimEmployee.SalesTerritoryKey to DimSalesTerritory.SalesTerritoryKey Diagram
Parent TableParent Column
DimSalesTerritory SalesTerritoryKey
Child TableChild Column
DimEmployee SalesTerritoryKey
3.12.2 fk DimEmployee.SalesTerritoryKey to DimSalesTerritory.SalesTerritoryKey Properties
PropertyValue
Name fk DimEmployee.SalesTerritoryKey to DimSalesTerritory.SalesTerritoryKey
Description
Parent TableDimSalesTerritory
Parent ColumnSalesTerritoryKey
Parent Cardinality 1
Child TableDimEmployee
Child ColumnSalesTerritoryKey
Child Cardinality 1..n
Comments
3.13 Relationship fk DimGeography.SalesTerritoryKey to DimSalesTerritory.SalesTerritoryKey
3.13.1 fk DimGeography.SalesTerritoryKey to DimSalesTerritory.SalesTerritoryKey Diagram
Parent TableParent Column
DimSalesTerritory SalesTerritoryKey
Child TableChild Column
DimGeography SalesTerritoryKey
3.13.2 fk DimGeography.SalesTerritoryKey to DimSalesTerritory.SalesTerritoryKey Properties
PropertyValue
Name fk DimGeography.SalesTerritoryKey to DimSalesTerritory.SalesTerritoryKey
Description
Parent TableDimSalesTerritory
Parent ColumnSalesTerritoryKey
Parent Cardinality 1
Child TableDimGeography
Child ColumnSalesTerritoryKey
Child Cardinality 1..n
Comments
3.14 Relationship fk DimOrganization.CurrencyKey to DimCurrency.CurrencyKey
3.14.1 fk DimOrganization.CurrencyKey to DimCurrency.CurrencyKey Diagram
Parent TableParent Column
DimCurrency CurrencyKey
Child TableChild Column
DimOrganization CurrencyKey
3.14.2 fk DimOrganization.CurrencyKey to DimCurrency.CurrencyKey Properties
PropertyValue
Name fk DimOrganization.CurrencyKey to DimCurrency.CurrencyKey
Description
Parent TableDimCurrency
Parent ColumnCurrencyKey
Parent Cardinality 1
Child TableDimOrganization
Child ColumnCurrencyKey
Child Cardinality 1..n
Comments
3.15 Relationship fk DimOrganization.OrganizationKey to FactFinance.OrganizationKey
3.15.1 fk DimOrganization.OrganizationKey to FactFinance.OrganizationKey Diagram
Parent TableParent Column
DimOrganization OrganizationKey
Child TableChild Column
FactFinance OrganizationKey
3.15.2 fk DimOrganization.OrganizationKey to FactFinance.OrganizationKey Properties
PropertyValue
Name fk DimOrganization.OrganizationKey to FactFinance.OrganizationKey
Description
Parent TableDimOrganization
Parent ColumnOrganizationKey
Parent Cardinality 1
Child TableFactFinance
Child ColumnOrganizationKey
Child Cardinality 0..n
Comments
3.16 Relationship fk DimOrganization.ParentOrganizationKey to DimOrganization.OrganizationKey
3.16.1 fk DimOrganization.ParentOrganizationKey to DimOrganization.OrganizationKey Diagram
Parent TableParent Column
DimOrganization OrganizationKey
Child TableChild Column
DimOrganization ParentOrganizationKey
3.16.2 fk DimOrganization.ParentOrganizationKey to DimOrganization.OrganizationKey Properties
PropertyValue
Name fk DimOrganization.ParentOrganizationKey to DimOrganization.OrganizationKey
Description
Parent TableDimOrganization
Parent ColumnOrganizationKey
Parent Cardinality 1
Child TableDimOrganization
Child ColumnParentOrganizationKey
Child Cardinality 1..n
Comments
3.17 Relationship fk DimProduct.ProductSubcategoryKey to DimProductSubcategory.ProductSubcategoryKey
3.17.1 fk DimProduct.ProductSubcategoryKey to DimProductSubcategory.ProductSubcategoryKey Diagram
3.17.2 fk DimProduct.ProductSubcategoryKey to DimProductSubcategory.ProductSubcategoryKey Properties
PropertyValue
Name fk DimProduct.ProductSubcategoryKey to DimProductSubcategory.ProductSubcategoryKey
Description
Parent TableDimProductSubcategory
Parent ColumnProductSubcategoryKey
Parent Cardinality 1
Child TableDimProduct
Child ColumnProductSubcategoryKey
Child Cardinality 1..n
Comments
3.18 Relationship fk DimProductSubcategory.ProductCategoryKey to DimProductCategory.ProductCategoryKey
3.18.1 fk DimProductSubcategory.ProductCategoryKey to DimProductCategory.ProductCategoryKey Diagram
3.18.2 fk DimProductSubcategory.ProductCategoryKey to DimProductCategory.ProductCategoryKey Properties
PropertyValue
Name fk DimProductSubcategory.ProductCategoryKey to DimProductCategory.ProductCategoryKey
Description
Parent TableDimProductCategory
Parent ColumnProductCategoryKey
Parent Cardinality 1
Child TableDimProductSubcategory
Child ColumnProductCategoryKey
Child Cardinality 1..n
Comments
3.19 Relationship fk DimReseller.GeographyKey to DimGeography.GeographyKey
3.19.1 fk DimReseller.GeographyKey to DimGeography.GeographyKey Diagram
Parent TableParent Column
DimGeography GeographyKey
Child TableChild Column
DimReseller GeographyKey
3.19.2 fk DimReseller.GeographyKey to DimGeography.GeographyKey Properties
PropertyValue
Name fk DimReseller.GeographyKey to DimGeography.GeographyKey
Description
Parent TableDimGeography
Parent ColumnGeographyKey
Parent Cardinality 1
Child TableDimReseller
Child ColumnGeographyKey
Child Cardinality 1..n
Comments
3.20 Relationship fk DimScenario.ScenarioKey to FactFinance.ScenarioKey
3.20.1 fk DimScenario.ScenarioKey to FactFinance.ScenarioKey Diagram
Parent TableParent Column
DimScenario ScenarioKey
Child TableChild Column
FactFinance ScenarioKey
3.20.2 fk DimScenario.ScenarioKey to FactFinance.ScenarioKey Properties
PropertyValue
Name fk DimScenario.ScenarioKey to FactFinance.ScenarioKey
Description
Parent TableDimScenario
Parent ColumnScenarioKey
Parent Cardinality 1
Child TableFactFinance
Child ColumnScenarioKey
Child Cardinality 0..n
Comments
3.21 Relationship fk FactInternetSales.CurrencyKey to DimCurrency.CurrencyKey
3.21.1 fk FactInternetSales.CurrencyKey to DimCurrency.CurrencyKey Diagram
Parent TableParent Column
DimCurrency CurrencyKey
Child TableChild Column
FactInternetSales CurrencyKey
3.21.2 fk FactInternetSales.CurrencyKey to DimCurrency.CurrencyKey Properties
PropertyValue
Name fk FactInternetSales.CurrencyKey to DimCurrency.CurrencyKey
Description
Parent TableDimCurrency
Parent ColumnCurrencyKey
Parent Cardinality 1
Child TableFactInternetSales
Child ColumnCurrencyKey
Child Cardinality 1..n
Comments
3.22 Relationship fk FactInternetSales.CustomerKey to DimCustomer.CustomerKey
3.22.1 fk FactInternetSales.CustomerKey to DimCustomer.CustomerKey Diagram
Parent TableParent Column
DimCustomer CustomerKey
Child TableChild Column
FactInternetSales CustomerKey
3.22.2 fk FactInternetSales.CustomerKey to DimCustomer.CustomerKey Properties
PropertyValue
Name fk FactInternetSales.CustomerKey to DimCustomer.CustomerKey
Description
Parent TableDimCustomer
Parent ColumnCustomerKey
Parent Cardinality 1
Child TableFactInternetSales
Child ColumnCustomerKey
Child Cardinality 1..n
Comments
3.23 Relationship fk FactInternetSales.DueDateKey to DimDate.DateKey
3.23.1 fk FactInternetSales.DueDateKey to DimDate.DateKey Diagram
Parent TableParent Column
DimDate DateKey
Child TableChild Column
FactInternetSales DueDateKey
3.23.2 fk FactInternetSales.DueDateKey to DimDate.DateKey Properties
PropertyValue
Name fk FactInternetSales.DueDateKey to DimDate.DateKey
Description
Parent TableDimDate
Parent ColumnDateKey
Parent Cardinality 1
Child TableFactInternetSales
Child ColumnDueDateKey
Child Cardinality 1..n
Comments
3.24 Relationship fk FactInternetSales.PromotionKey to DimPromotion.PromotionKey
3.24.1 fk FactInternetSales.PromotionKey to DimPromotion.PromotionKey Diagram
Parent TableParent Column
DimPromotion PromotionKey
Child TableChild Column
FactInternetSales PromotionKey
3.24.2 fk FactInternetSales.PromotionKey to DimPromotion.PromotionKey Properties
PropertyValue
Name fk FactInternetSales.PromotionKey to DimPromotion.PromotionKey
Description
Parent TableDimPromotion
Parent ColumnPromotionKey
Parent Cardinality 1
Child TableFactInternetSales
Child ColumnPromotionKey
Child Cardinality 1..n
Comments
3.25 Relationship fk FactInternetSales.SalesOrderNumber to FactInternetSalesReason.SalesOrderNumber
3.25.1 fk FactInternetSales.SalesOrderNumber to FactInternetSalesReason.SalesOrderNumber Diagram
3.25.2 fk FactInternetSales.SalesOrderNumber to FactInternetSalesReason.SalesOrderNumber Properties
PropertyValue
Name fk FactInternetSales.SalesOrderNumber to FactInternetSalesReason.SalesOrderNumber
Description
Parent TableFactInternetSalesReason
Parent ColumnSalesOrderNumber
Parent Cardinality 1
Child TableFactInternetSales
Child ColumnSalesOrderNumber
Child Cardinality 1..n
Comments
3.26 Relationship fk FactInternetSales.SalesTerritoryKey to DimSalesTerritory.SalesTerritoryKey
3.26.1 fk FactInternetSales.SalesTerritoryKey to DimSalesTerritory.SalesTerritoryKey Diagram
Parent TableParent Column
DimSalesTerritory SalesTerritoryKey
Child TableChild Column
FactInternetSales SalesTerritoryKey
3.26.2 fk FactInternetSales.SalesTerritoryKey to DimSalesTerritory.SalesTerritoryKey Properties
PropertyValue
Name fk FactInternetSales.SalesTerritoryKey to DimSalesTerritory.SalesTerritoryKey
Description
Parent TableDimSalesTerritory
Parent ColumnSalesTerritoryKey
Parent Cardinality 1
Child TableFactInternetSales
Child ColumnSalesTerritoryKey
Child Cardinality 1..n
Comments
3.27 Relationship fk FactInternetSales.ShipDateKey to DimDate.DateKey
3.27.1 fk FactInternetSales.ShipDateKey to DimDate.DateKey Diagram
Parent TableParent Column
DimDate DateKey
Child TableChild Column
FactInternetSales ShipDateKey
3.27.2 fk FactInternetSales.ShipDateKey to DimDate.DateKey Properties
PropertyValue
Name fk FactInternetSales.ShipDateKey to DimDate.DateKey
Description
Parent TableDimDate
Parent ColumnDateKey
Parent Cardinality 1
Child TableFactInternetSales
Child ColumnShipDateKey
Child Cardinality 1..n
Comments
3.28 Relationship fk FactInternetSalesReason.SalesReasonKey to DimSalesReason.SalesReasonKey
3.28.1 fk FactInternetSalesReason.SalesReasonKey to DimSalesReason.SalesReasonKey Diagram
Parent TableParent Column
DimSalesReason SalesReasonKey
Child TableChild Column
FactInternetSalesReason SalesReasonKey
3.28.2 fk FactInternetSalesReason.SalesReasonKey to DimSalesReason.SalesReasonKey Properties
PropertyValue
Name fk FactInternetSalesReason.SalesReasonKey to DimSalesReason.SalesReasonKey
Description
Parent TableDimSalesReason
Parent ColumnSalesReasonKey
Parent Cardinality 1
Child TableFactInternetSalesReason
Child ColumnSalesReasonKey
Child Cardinality 1..n
Comments
3.29 Relationship fk FactResellerSales.CurrencyKey to DimCurrency.CurrencyKey
3.29.1 fk FactResellerSales.CurrencyKey to DimCurrency.CurrencyKey Diagram
Parent TableParent Column
DimCurrency CurrencyKey
Child TableChild Column
FactResellerSales CurrencyKey
3.29.2 fk FactResellerSales.CurrencyKey to DimCurrency.CurrencyKey Properties
PropertyValue
Name fk FactResellerSales.CurrencyKey to DimCurrency.CurrencyKey
Description
Parent TableDimCurrency
Parent ColumnCurrencyKey
Parent Cardinality 1
Child TableFactResellerSales
Child ColumnCurrencyKey
Child Cardinality 1..n
Comments
3.30 Relationship fk FactResellerSales.DueDateKey to DimDate.DateKey
3.30.1 fk FactResellerSales.DueDateKey to DimDate.DateKey Diagram
Parent TableParent Column
DimDate DateKey
Child TableChild Column
FactResellerSales DueDateKey
3.30.2 fk FactResellerSales.DueDateKey to DimDate.DateKey Properties
PropertyValue
Name fk FactResellerSales.DueDateKey to DimDate.DateKey
Description
Parent TableDimDate
Parent ColumnDateKey
Parent Cardinality 1
Child TableFactResellerSales
Child ColumnDueDateKey
Child Cardinality 1..n
Comments
3.31 Relationship fk FactResellerSales.EmployeeKey to DimEmployee.EmployeeKey
3.31.1 fk FactResellerSales.EmployeeKey to DimEmployee.EmployeeKey Diagram
Parent TableParent Column
DimEmployee EmployeeKey
Child TableChild Column
FactResellerSales EmployeeKey
3.31.2 fk FactResellerSales.EmployeeKey to DimEmployee.EmployeeKey Properties
PropertyValue
Name fk FactResellerSales.EmployeeKey to DimEmployee.EmployeeKey
Description
Parent TableDimEmployee
Parent ColumnEmployeeKey
Parent Cardinality 1
Child TableFactResellerSales
Child ColumnEmployeeKey
Child Cardinality 1..n
Comments
3.32 Relationship fk FactResellerSales.PromotionKey to DimPromotion.PromotionKey
3.32.1 fk FactResellerSales.PromotionKey to DimPromotion.PromotionKey Diagram
Parent TableParent Column
DimPromotion PromotionKey
Child TableChild Column
FactResellerSales PromotionKey
3.32.2 fk FactResellerSales.PromotionKey to DimPromotion.PromotionKey Properties
PropertyValue
Name fk FactResellerSales.PromotionKey to DimPromotion.PromotionKey
Description
Parent TableDimPromotion
Parent ColumnPromotionKey
Parent Cardinality 1
Child TableFactResellerSales
Child ColumnPromotionKey
Child Cardinality 1..n
Comments
3.33 Relationship fk FactResellerSales.ResellerKey to DimReseller.ResellerKey
3.33.1 fk FactResellerSales.ResellerKey to DimReseller.ResellerKey Diagram
Parent TableParent Column
DimReseller ResellerKey
Child TableChild Column
FactResellerSales ResellerKey
3.33.2 fk FactResellerSales.ResellerKey to DimReseller.ResellerKey Properties
PropertyValue
Name fk FactResellerSales.ResellerKey to DimReseller.ResellerKey
Description
Parent TableDimReseller
Parent ColumnResellerKey
Parent Cardinality 1
Child TableFactResellerSales
Child ColumnResellerKey
Child Cardinality 1..n
Comments
3.34 Relationship fk FactResellerSales.SalesOrderNumber to FactInternetSalesReason.SalesOrderNumber
3.34.1 fk FactResellerSales.SalesOrderNumber to FactInternetSalesReason.SalesOrderNumber Diagram
3.34.2 fk FactResellerSales.SalesOrderNumber to FactInternetSalesReason.SalesOrderNumber Properties
PropertyValue
Name fk FactResellerSales.SalesOrderNumber to FactInternetSalesReason.SalesOrderNumber
Description
Parent TableFactInternetSalesReason
Parent ColumnSalesOrderNumber
Parent Cardinality 1
Child TableFactResellerSales
Child ColumnSalesOrderNumber
Child Cardinality 1..n
Comments
3.35 Relationship fk FactResellerSales.SalesTerritoryKey to DimSalesTerritory.SalesTerritoryKey
3.35.1 fk FactResellerSales.SalesTerritoryKey to DimSalesTerritory.SalesTerritoryKey Diagram
Parent TableParent Column
DimSalesTerritory SalesTerritoryKey
Child TableChild Column
FactResellerSales SalesTerritoryKey
3.35.2 fk FactResellerSales.SalesTerritoryKey to DimSalesTerritory.SalesTerritoryKey Properties
PropertyValue
Name fk FactResellerSales.SalesTerritoryKey to DimSalesTerritory.SalesTerritoryKey
Description
Parent TableDimSalesTerritory
Parent ColumnSalesTerritoryKey
Parent Cardinality 1
Child TableFactResellerSales
Child ColumnSalesTerritoryKey
Child Cardinality 1..n
Comments
3.36 Relationship fk FactResellerSales.ShipDateKey to DimDate.DateKey
3.36.1 fk FactResellerSales.ShipDateKey to DimDate.DateKey Diagram
Parent TableParent Column
DimDate DateKey
Child TableChild Column
FactResellerSales ShipDateKey
3.36.2 fk FactResellerSales.ShipDateKey to DimDate.DateKey Properties
PropertyValue
Name fk FactResellerSales.ShipDateKey to DimDate.DateKey
Description
Parent TableDimDate
Parent ColumnDateKey
Parent Cardinality 1
Child TableFactResellerSales
Child ColumnShipDateKey
Child Cardinality 1..n
Comments
3.37 Relationship fk FactSalesQuota.DateKey to DimDate.DateKey
3.37.1 fk FactSalesQuota.DateKey to DimDate.DateKey Diagram
Parent TableParent Column
DimDate DateKey
Child TableChild Column
FactSalesQuota DateKey
3.37.2 fk FactSalesQuota.DateKey to DimDate.DateKey Properties
PropertyValue
Name fk FactSalesQuota.DateKey to DimDate.DateKey
Description
Parent TableDimDate
Parent ColumnDateKey
Parent Cardinality 1
Child TableFactSalesQuota
Child ColumnDateKey
Child Cardinality 1..n
Comments
3.38 Relationship fk FactSalesQuota.EmployeeKey to DimEmployee.EmployeeKey
3.38.1 fk FactSalesQuota.EmployeeKey to DimEmployee.EmployeeKey Diagram
Parent TableParent Column
DimEmployee EmployeeKey
Child TableChild Column
FactSalesQuota EmployeeKey
3.38.2 fk FactSalesQuota.EmployeeKey to DimEmployee.EmployeeKey Properties
PropertyValue
Name fk FactSalesQuota.EmployeeKey to DimEmployee.EmployeeKey
Description
Parent TableDimEmployee
Parent ColumnEmployeeKey
Parent Cardinality 1
Child TableFactSalesQuota
Child ColumnEmployeeKey
Child Cardinality 1..n
Comments
3.39 Relationship fk FactSurveyResponse.CustomerKey to DimCustomer.CustomerKey
3.39.1 fk FactSurveyResponse.CustomerKey to DimCustomer.CustomerKey Diagram
Parent TableParent Column
DimCustomer CustomerKey
Child TableChild Column
FactSurveyResponse CustomerKey
3.39.2 fk FactSurveyResponse.CustomerKey to DimCustomer.CustomerKey Properties
PropertyValue
Name fk FactSurveyResponse.CustomerKey to DimCustomer.CustomerKey
Description
Parent TableDimCustomer
Parent ColumnCustomerKey
Parent Cardinality 1
Child TableFactSurveyResponse
Child ColumnCustomerKey
Child Cardinality 1..n
Comments
3.40 Relationship fk FactSurveyResponse.DateKey to DimDate.DateKey
3.40.1 fk FactSurveyResponse.DateKey to DimDate.DateKey Diagram
Parent TableParent Column
DimDate DateKey
Child TableChild Column
FactSurveyResponse DateKey
3.40.2 fk FactSurveyResponse.DateKey to DimDate.DateKey Properties
PropertyValue
Name fk FactSurveyResponse.DateKey to DimDate.DateKey
Description
Parent TableDimDate
Parent ColumnDateKey
Parent Cardinality 1
Child TableFactSurveyResponse
Child ColumnDateKey
Child Cardinality 1..n
Comments
3.41 Relationship fk FactSurveyResponse.ProductCategoryKey to DimProductCategory.ProductCategoryKey
3.41.1 fk FactSurveyResponse.ProductCategoryKey to DimProductCategory.ProductCategoryKey Diagram
3.41.2 fk FactSurveyResponse.ProductCategoryKey to DimProductCategory.ProductCategoryKey Properties
PropertyValue
Name fk FactSurveyResponse.ProductCategoryKey to DimProductCategory.ProductCategoryKey
Description
Parent TableDimProductCategory
Parent ColumnProductCategoryKey
Parent Cardinality 1
Child TableFactSurveyResponse
Child ColumnProductCategoryKey
Child Cardinality 1..n
Comments
3.42 Relationship fk FactSurveyResponse.ProductSubcategoryKey to DimProductSubcategory.ProductSubcategoryKey
3.42.1 fk FactSurveyResponse.ProductSubcategoryKey to DimProductSubcategory.ProductSubcategoryKey Diagram
3.42.2 fk FactSurveyResponse.ProductSubcategoryKey to DimProductSubcategory.ProductSubcategoryKey Properties
PropertyValue
Name fk FactSurveyResponse.ProductSubcategoryKey to DimProductSubcategory.ProductSubcategoryKey
Description
Parent TableDimProductSubcategory
Parent ColumnProductSubcategoryKey
Parent Cardinality 1
Child TableFactSurveyResponse
Child ColumnProductSubcategoryKey
Child Cardinality 1..n
Comments